package cn.com.nes.site.module.dao;

import cn.com.nes.common.util.*;
import cn.com.nes.common.wx.pay.PayUtil;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.Map.Entry;

@Repository
public class ActiveDaoImpl extends BaseDaoImpl {


    @SuppressWarnings("unchecked")
    public PageData saveActiveSession(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("参数信息保存失败");
        param.put("tableName", "active_session");
        PageData sqlInfo = null;
        String uuid = (String) param.get("uuid");
        String id = (String) param.get("id");
        param.put("companyId", req.getUinfo().getCompanyId());

        //是否为小程序默认模板
        String sfmr = (String) param.get("sfmr");
        if ("1".equals(sfmr)) {
            //将公司其他模板是否默认改为0
            StringBuffer sb = new StringBuffer();
            sb.append(" select uuid from active_session where companyId= '")
                    .append(req.getUinfo().getCompanyId())
                    .append("' ");
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            if (list.size() > 0) {
                sb.delete(0, sb.length());
                sb.append(" update active_session set sfmr ='0' where uuid in ('null'");
                for (int i = 0; i < list.size(); i++) {
                    sb.append(",'").append(list.get(i).get("uuid")).append("'");
                }
                sb.append(") ");
                this.update(conn, sb.toString(), new Object[]{});
            }
        }
        //保存活动信息
        if (uuid == null || "".equals(uuid)) {
            uuid = UUID.randomUUID().toString();
            param.put("uuid", uuid);
            sqlInfo = getInsertSQL(conn, req);
        } else {
            param.put("keys", "uuid");
            sqlInfo = getUpdateSQL(conn, req);
        }
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }


        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());

        //查询公司id和id
        Map<String, Object> ext = new LinkedHashMap<String, Object>();
        List<Map<String, Object>> list = this.queryForList(conn, "select id,companyId from active_session where uuid=?", new Object[]{uuid});
        if (list.size() == 1) {
            ext.put("id", list.get(0).get("id"));
            ext.put("companyId", list.get(0).get("companyId"));
        }

        res.setRetCode(0);
        res.setRetMessage("OK");
        res.setKey((String) param.get("uuid"));

        res.setDatas(ext);

        return res;
    }

    @SuppressWarnings({"unchecked"})
    public PageData getActiveSession(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        sb.append(" select cluesource,sffb,if(sffb='1','是','否') sffbName,forward,maxForward,uuid,companyId,activeName,date_format(activeTimeStart,'%Y-%m-%d %H:%i:%s') activeTimeStart," + "date_format(activeTimeEnd,'%Y-%m-%d %H:%i:%s') activeTimeEnd,activeCjcs,activeRule,titleUrl,backgroundUrl," + "mineFormMesg,indexTemplate,sfmr,if(sfmr='1','是','否') sfmrName,(SELECT count(distinct(userUuid)) FROM active_record m where m.activeUuid=a.uuid) cnt from active_session a where 1=1 ");
        if (!StringUtils.isEmpty((String) param.get("uuid"))) {
            sb.append(" and uuid= '").append(param.get("uuid")).append("' ");
        }
        if (!"模板".equals((String) param.get("isTemplate"))) {
            if (!StringUtils.isEmpty((String) param.get("key"))) {
                sb.append(" and activeName like '%").append(param.get("key")).append("%' ");
            }
            if (!StringUtils.isEmpty(req.getUinfo().getCompanyId())) {
                sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ");
            }
        }
        sb.append(" order by createtime desc ");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        if (list.size() > 0) {
            for (int j = 0; j < list.size(); j++) {
                String StringSession = list.get(j).get("mineFormMesg").toString();
                JSONObject jsonObject = (JSONObject) JSON.parse(StringSession);
                Map<String, Object> map = JSONObject.parseObject(jsonObject.toJSONString(), Map.class);
                if (!StringUtils.isEmpty((String) param.get("uuid"))) {
                    String titleName = list.get(0).get("titleUrl").toString();
                    String titleUrl = OssUtil.getImgSignURL(titleName, 0);
                    Map<String, String> titleImg = new HashMap<String, String>();
                    titleImg.put("name", titleName);
                    titleImg.put("url", titleUrl);
                    ArrayList<Object> titleUrlMap = new ArrayList<Object>();
                    titleUrlMap.add(0, titleImg);

                    String backgroundName = list.get(0).get("backgroundUrl").toString();
                    String backgroundUrl = OssUtil.getImgSignURL(backgroundName, 0);
                    Map<String, String> backgroundImg = new HashMap<String, String>();
                    backgroundImg.put("name", backgroundName);
                    backgroundImg.put("url", backgroundUrl);
                    ArrayList<Object> backgroundUrlMap = new ArrayList<Object>();
                    backgroundUrlMap.add(0, backgroundImg);

                    List<Map<String, Object>> jplist = (List<Map<String, Object>>) map.get("jplist");
                    for (int i = 0; i < jplist.size(); i++) {
                        String images = jplist.get(i).get("jpUrl").toString();
                        if (!StringUtils.isEmpty(images)) {
                            String[] imageList = images.split(";", -1);
                            List<String> imgURL = new ArrayList<String>();
                            for (int k = 0; k < imageList.length; k++) {
                                String str = imageList[k];
                                imgURL.add(OssUtil.getImgSignURL(str, 0));
                            }
                            Map<String, String> jpImg = new HashMap<String, String>();
                            jpImg.put("name", imageList[0]);
                            jpImg.put("url", imgURL.get(0));
                            ArrayList<Object> jpUrlMap = new ArrayList<Object>();
                            jpUrlMap.add(0, jpImg);
                            jplist.get(i).put("jpUrlMap", jpUrlMap);
                            //查询已中奖奖品数量
                            if (!StringUtils.isEmpty((String) param.get("uuid"))) {
                                jplist.get(i)
                                        .put("prizescnt_out", getActiveSessionLeftCnt(conn, (String) jplist.get(i)
                                                .get("jpname"), (String) param.get("uuid")));
                            }
                        }
                    }
                    list.get(j).put("titleUrlMap", titleUrlMap);
                    list.get(j).put("backgroundUrlMap", backgroundUrlMap);
                    list.get(j).put("jplist", jplist);
                }
            }
        }

        //查询参与人数


        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public int getActiveSessionLeftCnt(Connection conn, String jpname, String uuid) throws SQLException {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  count(1) cnt from active_record where sfwjp='是' and activeUuid=? and prizeName=? ");
        int cnt = this.queryForInt(conn, sb.toString(), new Object[]{uuid, jpname});
        return cnt;
    }

    public PageData delActiveSession(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("删除参数信息失败");
        String uuid = (String) param.get("uuid");
        if (!StringUtils.isEmpty(uuid)) {
            StringBuffer sb = new StringBuffer();
            sb.append("delete from active_session where uuid='").append(uuid).append("' ");
            this.update(conn, sb.toString(), new Object[]{});
            res.setRetCode(0);
            res.setRetMessage("OK");
        } else {
            return res;
        }

        return res;
    }

    public PageData getActiveUser(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        sb.append(" select * from ( ");
        sb.append(" select activeName,activeUuid,address,companyId,date_format(createTime, '%Y-%m-%d %T') as createTime,isPay,numberhome,prizeName,prizeRule,prizeType,sfwjp,userName,userPhone,userUuid,uuid  from active_record ")
                .append(" where companyId = '")
                .append(req.getUinfo().getCompanyId())
                .append("' ");

        if (!StringUtils.isEmpty((String) req.getReqData().get("isWinPrize")) && "是".equals(req.getReqData()
                .get("isWinPrize"))) {
            sb.append(" and sfwjp = '是' ");
        }

        if (!StringUtils.isEmpty((String) req.getReqData().get("activeUuid"))) {
            sb.append(" and activeUuid = '").append(req.getReqData().get("activeUuid")).append("' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("userName"))) {
            sb.append(" and (userName like '%")
                    .append(req.getReqData().get("userName"))
                    .append("%' or userPhone like '%")
                    .append(req.getReqData().get("userName"))
                    .append("%') ");
        }
        sb.append(" group by userUuid,activeUuid order by createTime desc");
        sb.append(" )x ");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData delActiveUser(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("删除参数信息失败");

        String uuid = (String) param.get("uuid");
        if (!StringUtils.isEmpty(uuid)) {
            StringBuffer sb = new StringBuffer();
            sb.append("delete from active_user where uuid='").append(uuid).append("' ");
            this.update(conn, sb.toString(), new Object[]{});
            res.setRetCode(0);
            res.setRetMessage("OK");
        } else {
            return res;
        }

        return res;
    }

    public PageData getActiveRecord(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        sb.append(" SELECT a.uuid,a.companyId,a.activeUuid,a.activeName,a.userUuid,a.userName,a.prizeName,userPhone,date_format(a.createTime,'%Y-%m-%d %H:%i:%s') createTime," + "date_format(DATE_ADD(a.createTime,INTERVAL 15 DAY),'%Y-%m-%d %H:%i:%s') prizeTimeEnd," + "if(a.prizeType='02' or a.isPay='1','02',if(NOW()>date_format(DATE_ADD(a.createTime,INTERVAL 15 DAY),'%Y-%m-%d %H:%i:%s'),'03','01')) prizeType," + "if(a.prizeType='02' or a.isPay='1','已兑换',if(NOW()>date_format(DATE_ADD(a.createTime,INTERVAL 15 DAY),'%Y-%m-%d %H:%i:%s'),'已过期','待兑换')) prizeTypeName ");
        sb.append(" FROM active_record a WHERE 1=1 ");
        if (!StringUtils.isEmpty((String) req.getReqData().get("activeUuid"))) {
            sb.append(" and a.activeUuid = '").append(req.getReqData().get("activeUuid")).append("' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("userUuid"))) {
            sb.append(" and a.userUuid = '").append(req.getReqData().get("userUuid")).append("' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("userName"))) {
            sb.append(" and a.userName like '%").append(req.getReqData().get("userName")).append("%' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("prizeName"))) {
            sb.append(" and a.prizeName like '%").append(req.getReqData().get("prizeName")).append("%' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("prizeType"))) {
            sb.append(" and if(a.prizeType='02' or a.isPay='1','02',if(NOW()>date_format(DATE_ADD(a.createTime,INTERVAL 15 DAY),'%Y-%m-%d %H:%i:%s'),'03','01')) = '")
                    .append(req.getReqData().get("prizeType"))
                    .append("' ");
        }
        if (!StringUtils.isEmpty((String) req.getUinfo().getCompanyId()) && !req.getUinfo()
                .getCompanyId()
                .equals("0000")) {
            sb.append(" and a.companyId = '").append(req.getUinfo().getCompanyId()).append("' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("sfwjp"))) {
            sb.append(" and a.sfwjp = '").append(req.getReqData().get("sfwjp")).append("' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("phone"))) {
            sb.append(" and a.userPhone like '%").append(req.getReqData().get("phone")).append("%' ");
        }
        sb.append(" order by createTime desc");
        System.out.println(sb);
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" FROM ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData delActiveRecord(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("删除参数信息失败");
        String uuid = (String) param.get("uuid");
        if (!StringUtils.isEmpty(uuid)) {
            StringBuffer sb = new StringBuffer();
            sb.append("delete from active_record where uuid='").append(uuid).append("' ");
            this.update(conn, sb.toString(), new Object[]{});
            res.setRetCode(0);
            res.setRetMessage("OK");
        } else {
            return res;
        }

        return res;
    }

    @SuppressWarnings({"unchecked"})
    public PageData getActiveSessionApp(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        sb.append(" select * from active_session where ").append(" uuid= '").append(param.get("uuid")).append("' ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        if (list.size() > 0) {
            for (int j = 0; j < list.size(); j++) {
                String StringSession = list.get(j).get("mineFormMesg").toString();
                JSONObject jsonObject = (JSONObject) JSON.parse(StringSession);
                Map<String, Object> map = JSONObject.parseObject(jsonObject.toJSONString(), Map.class);
                String titleName = list.get(0).get("titleUrl").toString();
                String titleUrl = OssUtil.getImgSignURL(titleName, 0);
                Map<String, String> titleImg = new HashMap<String, String>();
                titleImg.put("name", titleName);
                titleImg.put("url", titleUrl);
                ArrayList<Object> titleUrlMap = new ArrayList<Object>();
                titleUrlMap.add(0, titleImg);

                String backgroundName = list.get(0).get("backgroundUrl").toString();
                String backgroundUrl = OssUtil.getImgSignURL(backgroundName, 0);
                Map<String, String> backgroundImg = new HashMap<String, String>();
                backgroundImg.put("name", backgroundName);
                backgroundImg.put("url", backgroundUrl);
                ArrayList<Object> backgroundUrlMap = new ArrayList<Object>();
                backgroundUrlMap.add(0, backgroundImg);

                List<Map<String, Object>> jplist = (List<Map<String, Object>>) map.get("jplist");
                for (int i = 0; i < jplist.size(); i++) {
                    String images = jplist.get(i).get("jpUrl").toString();
                    if (!StringUtils.isEmpty(images)) {
                        String[] imageList = images.split(";", -1);
                        List<String> imgURL = new ArrayList<String>();
                        for (int k = 0; k < imageList.length; k++) {
                            String str = imageList[k];
                            imgURL.add(OssUtil.getImgSignURL(str, 0));
                        }
                        Map<String, String> jpImg = new HashMap<String, String>();
                        jpImg.put("name", imageList[0]);
                        jpImg.put("url", imgURL.get(0));
                        ArrayList<Object> jpUrlMap = new ArrayList<Object>();
                        jpUrlMap.add(0, jpImg);
                        jplist.get(i).put("jpUrlMap", jpUrlMap);
                    }
                }
                list.get(j).put("titleUrlMap", titleUrlMap);
                list.get(j).put("backgroundUrlMap", backgroundUrlMap);
                list.get(j).put("jplist", jplist);
            }
        }
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getUserInfo(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        //判断验证码是否正确
        String phone = (String) param.get("phone");
        String code = (String) param.get("code");
        boolean validCode = ValidCodeUtil.validCode(phone, code);
        if (!validCode) {
            res.setRetCode(9);
            res.setRetMessage("验证码输入错误!");
            return res;
        }
        StringBuffer sb = new StringBuffer();
        sb.append(" select * from active_user where  phone = ? ");
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{param.get("phone")});
        for (Map<String, Object> map : list) {
            String imageString = (String) map.get("userPic");
            if (!StringUtils.isEmpty(imageString)) {
                String ossImage = OssUtil.getImgSignURL(imageString, 0);
                map.put("userPic", ossImage);
            }
        }
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getUserCjcs(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        Map<String, Object> extData = new LinkedHashMap<String, Object>();

        StringBuffer sb = new StringBuffer();
        sb.append(" select count(0) userCjcs from active_record where userUuid =? and activeUuid=? ");
        int userCjcs = this.queryForInt(conn, sb.toString(), new Object[]{param.get("userUuid"), param.get("activeUuid")});
        extData.put("userCjcs", userCjcs);
        res.setExtData(extData);
        res.setRetCode(0);
        res.setRetMessage("OK");
        System.out.println("getUserCjcs=" + JsonUtil.getStriFromObj(res));

        return res;
    }

    public PageData getPrizeReceive(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        //查询礼物获得情况
        StringBuffer sb = new StringBuffer();
        sb.append(" select count(0) cnt,prizeName from active_record where activeUuid=?  group by prizeName");
        List<Map<String, Object>> prizeReceiveList = this.queryForList(conn, sb.toString(), new Object[]{param.get("activeUuid")});
        res.setPage(prizeReceiveList);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getActiveRecordApp(Connection conn, Connection conn_mall, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT couponUserId userCouponId,isShoppingMalls,couponId,date_format(DATE_ADD(a.createTime,INTERVAL 15 DAY),'%Y-%m-%d %H:%i:%s') prizeTimeEnd,a.prizeRule,a.userPhone,a.address," + "a.uuid,a.companyId,a.activeUuid,a.activeName,a.userUuid,a.userName,a.prizeName,date_format(a.createTime,'%Y-%m-%d %H:%i:%s') createTime," + "if(a.prizeType='02' or a.isPay='1','02',if(NOW()>date_format(DATE_ADD(a.createTime,INTERVAL 15 DAY),'%Y-%m-%d %H:%i:%s'),'03','01')) prizeType," + "if(a.prizeType='02' or a.isPay='1','已兑换',if(NOW()>date_format(DATE_ADD(a.createTime,INTERVAL 15 DAY),'%Y-%m-%d %H:%i:%s'),'已过期','待兑换')) prizeTypeName ");
        sb.append(" FROM active_record a WHERE 1=1 ");
        if (!StringUtils.isEmpty((String) req.getReqData().get("uuid"))) {
            sb.append(" and a.uuid = '").append(req.getReqData().get("uuid")).append("' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("activeUuid"))) {
            sb.append(" and a.activeUuid = '").append(req.getReqData().get("activeUuid")).append("' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("userUuid"))) {
            sb.append(" and a.userUuid = '").append(req.getReqData().get("userUuid")).append("' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("sfwjp"))) {//我的奖品
            sb.append(" and a.sfwjp = '是' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("companyId"))) {
            sb.append(" and a.companyId = '").append(req.getReqData().get("companyId")).append("' ");
        }
        if (!StringUtils.isEmpty((String) req.getReqData().get("prizeType"))) {
            sb.append(" and if(a.prizeType='02' or a.isPay='1','02',if(NOW()>date_format(DATE_ADD(a.createTime,INTERVAL 15 DAY),'%Y-%m-%d %H:%i:%s'),'03','01')) = '")
                    .append(req.getReqData().get("prizeType"))
                    .append("' ");
        }
        sb.append(" order by a.createTime desc ");
        //System.out.println(sb);
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        //如果活动ID是空，则为我的奖品，需要查PC端活动
        if ((StringUtils.isEmpty((String) req.getReqData()
                .get("activeUuid")) && !StringUtils.isEmpty((String) req.getReqData()
                .get("userUuid"))) || (!StringUtils.isEmpty((String) req.getReqData().get("uuid")))) {
            sb = new StringBuffer();
            sb.append("select '' userCouponId,date_format(DATE_ADD(a.createTime, INTERVAL 1 DAY),'%Y-%m-%d %H:%i:%s') prizeTimeEnd," + "'' prizeRule,c.cluephone userPhone,'' address,a.uuid,d.companyId,a.activeId activeUuid,e.name activeName,d.uuid userUuid,d.name userName,CONCAT(b.name,':',b.prize) prizeName," + "date_format(a.createTime,'%Y-%m-%d %H:%i:%s') createTime,'04' prizeType,'现场抽奖' prizeTypeName " + " from pc_active_award_record a " + " left join pc_active_reward b on a.rewardId=b.uuid " + " left join clue c on a.clueId=c.uuid " + " left join active_user d on c.cluephone=d.phone " + " left join pc_active e on a.activeId=e.uuid " + " where a.isdelete=0 ");
            if (!StringUtils.isEmpty((String) req.getReqData().get("uuid"))) {
                sb.append(" and a.uuid = '").append(req.getReqData().get("uuid")).append("' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("userUuid"))) {
                sb.append(" and d.uuid = '").append(req.getReqData().get("userUuid")).append("' ");
            }
            List<Map<String, Object>> listPc = this.queryForList(conn, sb.toString(), new Object[]{});
            list.addAll(0, listPc);
        }
        //循环list
        if (list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                String userCouponId = list.get(i).get("userCouponId").toString();
                if (!StringUtils.isEmpty(userCouponId)) {
                    //查询优惠卷money、minPrice，跳转使用
                    sb.delete(0, sb.length());
                    sb.append(" SELECT a.money,a.min_price minPrice,b.name merName,b.address_detail addressDetail FROM eb_coupon_user a " + " LEFT JOIN eb_merchant b on a.mer_id=b.id " + " where a.id=? ");
                    List<Map<String, Object>> listcop = this.queryForList(conn_mall, sb.toString(), new Object[]{userCouponId});
                    list.get(i).put("money", listcop.get(0).get("money"));
                    list.get(i).put("minPrice", listcop.get(0).get("minPrice"));
                    list.get(i).put("addressDetail", listcop.get(0).get("addressDetail"));
                    list.get(i).put("merName", listcop.get(0).get("merName"));
                }
            }
        }
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData saveActiveUser(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("参数信息保存失败");
        param.put("tableName", "active_user");
        PageData sqlInfo = null;
        String uuid = (String) param.get("uuid");
        param.put("yhlb", "01");

        if (uuid == null || "".equals(uuid)) {
            uuid = UUID.randomUUID().toString();
            param.put("uuid", uuid);
            sqlInfo = getInsertSQL(conn, req);
        } else {
            param.put("keys", "uuid");
            sqlInfo = getUpdateSQL(conn, req);
        }
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());

        res.setRetCode(0);
        res.setRetMessage("OK");
        res.setKey(uuid);
        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData saveAddress(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("参数信息保存失败");
        param.put("tableName", "active_record");
        param.put("keys", "uuid");
        String uuid = (String) param.get("uuid");
        PageData sqlInfo = null;

        //判断是否超郭抽奖次数
        PageData resTmp = getActiveLeftCnt(conn, req);
        if (resTmp.getRetCode() != 0) {
            return resTmp;
        }
        Integer cnt = (Integer) resTmp.getExtData();
        if (cnt == 0) {
            res.setRetCode(9);
            res.setRetMessage("您已超过允许的抽奖次数！");
            return res;
        }

        //获取号码归属地
        //			if(!StringUtils.isEmpty((String) param.get("userPhone"))) {
        //				Map<String, String> phoneAddress = HttpUtils.getPhoneAddress((String)param.get("userPhone"));
        //				Iterator<Entry<String, String>> iterator = phoneAddress.entrySet().iterator();
        //				List<String> phoneList = new ArrayList<String>();
        //				while(iterator.hasNext()) {
        //					Entry<String, String> next = iterator.next();
        //					phoneList.add(next.getValue());
        //				}
        //				String join = StringUtils.join(phoneList," ");
        //				param.put("numberhome", join);
        //			}

        sqlInfo = getUpdateSQL(conn, req);
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());

        //如果获取的是现金红包，那么发红包
        String prizeName = (String) param.get("prizeName");
        if (prizeName != null && prizeName.indexOf("红包") >= 0) {
            String amount = DBUtils.getNumbers(prizeName);
            List<Map<String, Object>> list = this.queryForList(conn, "SELECT b.openid,b.`name`,c.id activeUuid ,c.activeName,b.phone from  active_record a INNER JOIN active_user b on a.userUuid=b.uuid INNER JOIN active_session c on a.activeUuid=c.uuid where b.openid is not null and b.openid<>'' and b.phone is not null and b.phone<>''  and a.uuid=?", new Object[]{uuid});
            if (list.size() == 1) {
                String openId = (String) list.get(0).get("openid");
                String phone = (String) list.get(0).get("phone");
                System.currentTimeMillis();
                String activityUUid = list.get(0).get("activeUuid").toString();
                String orderNo = "ORD" + phone + System.currentTimeMillis();
                String activityName = (String) list.get(0).get("activeName");
                String nickName = (String) list.get(0).get("name");
                String ip = StringUtils.isEmpty((String) param.get("remoteIp")) ? "127.0.0.1" : (String) param.get("remoteIp");
                resTmp = PayUtil.payToUser(ip, openId, amount, activityUUid, activityName, nickName, orderNo);
                if (resTmp.getRetCode() == 0) {
                    //更新支付表
                    this.update(conn, "update active_record set isPay=1 where uuid=?  ", new Object[]{uuid});
                } else {
                    res.setRetCode(1);
                    res.setRetMessage("红包于10分钟之内到账，请注意查收！");
                    res.setKey(uuid);
                    return res;
                }
                System.out.println(JsonUtil.getStriFromObj("支付结果:" + JsonUtil.getStriFromObj(resTmp)));
            }
        }
        res.setRetCode(0);
        res.setRetMessage("OK");
        res.setKey(uuid);

        return res;
    }

    public PageData getPCActiveList(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        sb.append("select a.uuid,a.activeTypeId,a.name,a.createTime,a.memo\n" + " from pc_active a\n" + "where a.isdelete=0 and a.companyId='" + req.getUinfo()
                .getCompanyId() + "'");
        if (!StringUtils.isAnyEmpty((String) param.get("name"))) {
            sb.append(" and a.name like '%").append(param.get("name")).append("%'");
        }
        sb.append(" order by a.createTime desc ");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData getPCActiveDetail(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取活动信息失败");
        Map<String, Object> map = new HashMap();

        StringBuffer sb = new StringBuffer();
        //获取活动信息
        sb.delete(0, sb.length());
        sb.append("select a.uuid,a.activeTypeId,a.name,a.memo ")
                .append(" from pc_active a ")
                .append(" where a.isdelete=0 and a.companyId='")
                .append(req.getUinfo().getCompanyId())
                .append("'")
                .append(" and a.uuid='")
                .append(param.get("uuid"))
                .append("' ");
        List<Map<String, Object>> activeList = this.queryForList(conn, sb.toString(), new Object[]{});
        map.put("activeInfo", activeList.get(0));

        //获取奖品奖项列表
        sb.delete(0, sb.length());
        sb.append("select a.uuid,a.name,a.prize,a.prizeImg,a.chooseNumber \n" + "from pc_active_reward a\n" + "where a.isdelete=0 and a.companyId='")
                .append(req.getUinfo().getCompanyId())
                .append("'")
                .append(" and a.activeId='")
                .append(param.get("uuid"))
                .append("' ")
                .append(" order by a.serial asc");
        List<Map<String, Object>> prizeList = this.queryForList(conn, sb.toString(), new Object[]{});
        map.put("prizeList", prizeList);

        //获取内定人员信息
        sb.delete(0, sb.length());
        sb.append("select a.rewardId,GROUP_CONCAT(a.clueId) users \n" + " from pc_active_lucky_user a\n" + " where a.isdelete=0 and a.companyId='")
                .append(req.getUinfo().getCompanyId())
                .append("'");
        sb.append(" and a.activeId='").append(param.get("uuid")).append("' ");
        sb.append(" GROUP BY a.rewardId ");
        List<Map<String, Object>> luckyList = this.queryForList(conn, sb.toString(), new Object[]{});
        List<Map<String, Object>> luckyUser = new ArrayList<Map<String, Object>>();

        for (int i = 0; i < luckyList.size(); i++) {
            Map<String, Object> luckyMap = new HashMap<String, Object>();
            String rewardId = String.valueOf(luckyList.get(i).get("rewardId"));
            String[] users = String.valueOf(luckyList.get(i).get("users")).split(",");
            luckyMap.put("rewardId", rewardId);
            luckyMap.put("user", new ArrayList<String>(Arrays.asList(users)));
            luckyUser.add(luckyMap);
        }
        map.put("luckyUser", luckyUser);
        res.setExtData(map);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    @SuppressWarnings("unchecked")
    public PageData savePCActive(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();

        //保存PC活动信息
        DataReq activeReq = new DataReq();
        HashMap<String, Object> activeData = new HashMap();
        JSONObject activeInfo = (JSONObject) param.get("activeInfo");
        activeData = (HashMap<String, Object>) JSONObject.parseObject(activeInfo.toJSONString(), Map.class);
        activeData.put("companyId", req.getUinfo().getCompanyId());
        activeReq.setReqData(activeData);
        activeReq.setUinfo(req.getUinfo());
        res = savePCActiveInfo(conn, activeReq);
        if (res.getRetCode() != 0) {
            return res;
        }

        //获取活动uuid
        String activeUuid = res.getKey();

        //保存PC奖项信息
        DataReq prizeReq = new DataReq();
        HashMap<String, Object> prizeData = new HashMap();
        prizeData.put("activeId", activeUuid);
        prizeData.put("companyId", req.getUinfo().getCompanyId());
        JSONArray prizeArray = (JSONArray) param.get("prizeList");
        //封装prizeList
        Iterator prizeIter = prizeArray.listIterator();
        List<Map<String, Object>> prizeList = new ArrayList();
        while (prizeIter.hasNext()) {
            Map<String, Object> obj = (Map<String, Object>) prizeIter.next();
            prizeList.add(obj);
        }
        prizeData.put("prizeList", prizeList);

        prizeReq.setReqData(prizeData);
        prizeReq.setUinfo(req.getUinfo());
        res = savePCPrizeInfo(conn, prizeReq);
        if (res.getRetCode() != 0) {
            return res;
        }

        res.setKey(activeUuid);

        return res;
    }

    //保存PC活动信息
    public PageData savePCActiveInfo(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存活动信息失败");
        param.put("tableName", "pc_active");
        PageData sqlInfo = null;

        String uuid = (String) param.get("uuid");
        if (uuid == null || "".equals(uuid)) {
            param.put("isdelete", "0");
            uuid = UUID.randomUUID().toString();
            param.put("uuid", uuid);
            sqlInfo = getInsertSQL(conn, req);
        } else {
            param.put("keys", "uuid");
            sqlInfo = getUpdateSQL(conn, req);
        }
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());

        res.setKey((String) param.get("uuid"));
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    //保存PC奖项信息
    public PageData savePCPrizeInfo(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存奖项信息失败");
        param.put("tableName", "pc_active_reward");
        PageData sqlInfo = null;

        //根据活动ID删除奖项
        StringBuffer sb = new StringBuffer();
        sb.append("delete from pc_active_reward where activeId='").append(param.get("activeId")).append("' ");
        int result = this.update(conn, sb.toString(), new Object[]{});
        List<Map<String, Object>> prizeList = (List<Map<String, Object>>) param.get("prizeList");
        for (int i = 0; i < prizeList.size(); i++) {
            String uuid = (String) prizeList.get(i).get("uuid");
            param.put("name", prizeList.get(i).get("name"));
            param.put("prize", prizeList.get(i).get("prize"));
            param.put("prizeImg", prizeList.get(i).get("prizeImg"));
            param.put("chooseNumber", prizeList.get(i).get("chooseNumber"));

            param.put("isdelete", "0");
            if (uuid == null || "".equals(uuid)) {
                uuid = UUID.randomUUID().toString();
            }
            param.put("uuid", uuid);
            sqlInfo = getInsertSQL(conn, req);

            //					if (uuid ==null || "".equals(uuid)){
            //						param.put("isdelete", "0");
            //						uuid = UUID.randomUUID().toString();
            //						param.put("uuid", uuid);
            //						sqlInfo = getInsertSQL(req);
            //					}else{
            //						param.put("uuid", uuid);
            //						param.put("keys", "uuid") ;
            //						sqlInfo = getUpdateSQL(req);
            //					}
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.update(conn, sb_field.toString(), listData.toArray());
        }


        res.setKey((String) param.get("uuid"));
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    //保存PC内定用户信息
    @SuppressWarnings("unchecked")
    public PageData savePCLuckyUser(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存内定用户信息失败");
        param.put("tableName", "pc_active_lucky_user");
        PageData sqlInfo = null;

        //根据activeId删除内定用户
        StringBuffer sb = new StringBuffer();
        sb.append("delete from pc_active_lucky_user where activeId='").append(param.get("activeId")).append("' ");
        this.update(conn, sb.toString(), new Object[]{});

        param.put("companyId", req.getUinfo().getCompanyId());
        param.put("isdelete", "0");
        //新增内定用户
        JSONArray luckyUserArray = (JSONArray) param.get("list");
        //封装luckyUser
        Iterator luckyUserIter = luckyUserArray.listIterator();
        while (luckyUserIter.hasNext()) {
            Map<String, Object> obj = (Map<String, Object>) luckyUserIter.next();
            String rewardId = String.valueOf(obj.get("rewardId"));
            param.put("rewardId", rewardId);
            List<String> users = (List<String>) obj.get("user");
            for (int i = 0; i < users.size(); i++) {
                param.remove("uuid");
                param.put("clueId", users.get(i));
                sqlInfo = getInsertSQL(conn, req);
                if (sqlInfo.getRetCode() != 0) {
                    return sqlInfo;
                }
                final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
                final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
                this.update(conn, sb_field.toString(), listData.toArray());
            }
        }

        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    //PC-新增流量来源
    @SuppressWarnings("unchecked")
    public PageData addLy(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("新增流量来源失败");
        param.put("tableName", "t_param");
        PageData sqlInfo = null;

        //查询流量来源最新代码
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT `value` from t_param WHERE param_type='13' ORDER BY `value` desc LIMIT 1  ");
        int xh = this.queryForInt(conn, sb.toString(), new Object[]{});
        int value = xh + 1;
        param.put("param_type", "13");
        param.put("type_name", "流量来源");
        param.put("name", req.getReqData().get("lyname"));
        param.put("value", value);
        param.put("is_delete", "0");
        param.put("companyId", req.getUinfo().getCompanyId());

        sqlInfo = getInsertSQL(conn, req);
        if (sqlInfo.getRetCode() != 0) {

            return sqlInfo;
        }

        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());

        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    //PC-新增奖品图片
    @SuppressWarnings("unchecked")
    public PageData addTp(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("新增奖品图片失败");
        param.put("tableName", "active_prize");
        PageData sqlInfo = null;

        param.put("name", req.getReqData().get("tpname"));
        param.put("url", req.getReqData().get("tpurl"));

        sqlInfo = getInsertSQL(conn, req);
        if (sqlInfo.getRetCode() != 0) {

            return sqlInfo;
        }

        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());

        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getPrizeTp(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        sb.append(" select name,url value from active_prize ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        if (list.size() > 0) {
            for (int j = 0; j < list.size(); j++) {
                String titleName = list.get(j).get("value").toString();
                String titleUrl = OssUtil.getImgSignURL(titleName, 1);
                list.get(j).put("url", titleUrl);
            }
        }
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData delPCActive(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("删除活动失败");

        String uuid = (String) param.get("uuid");
        if (!StringUtils.isEmpty(uuid)) {
            StringBuffer sb = new StringBuffer();
            sb.append("update pc_active set isdelete=1 where uuid='").append(uuid).append("' ");
            this.update(conn, sb.toString(), new Object[]{});
            res.setRetCode(0);
            res.setRetMessage("OK");
        } else {

            return res;
        }

        return res;
    }

    @SuppressWarnings({"unchecked", "rawtypes"})
    public PageData getPCActiveShow(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取活动预览信息失败");
        Map<String, Object> map = new HashMap();
        StringBuffer sb = new StringBuffer();
        //获取活动信息
        sb.delete(0, sb.length());
        sb.append("select a.uuid id,a.name from pc_active a where a.isdelete=0 and a.companyId='")
                .append(req.getUinfo().getCompanyId())
                .append("'")
                .append(" and a.uuid='")
                .append(param.get("uuid"))
                .append("' ");
        List<Map<String, Object>> activeList = this.queryForList(conn, sb.toString(), new Object[]{});
        map.put("active", activeList.size() > 0 ? activeList.get(0) : null);
        //获取活动人员信息
        if (!StringUtils.isEmpty((String) param.get("islook"))) {
            if ("look".equals(param.get("islook"))) { //预览
                sb.delete(0, sb.length());
                sb.append(" select l.id,l.image,l.thumb_image,l.name,l.phone,concat(left(l.phone,3),'****',right(l.phone,4)) tmPhone from( ");
                sb.append(" select a.uuid id,if(a.pic<>'' and a.pic is not null,a.pic,'') image,if(a.pic<>'' and a.pic is not null,a.pic,'') thumb_image,a.name,a.phone " + " from pc_active_default_user a " + " where a.isdelete=0 ");
                sb.append(" union ");
                sb.append("select a.uuid id,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') image,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') thumb_image,a.companycontact name,a.cluephone phone " + " from clue a " + " left join active_user b on a.openid=b.openid and yhlb='02' " + " where a.isdelete=0 and b.unionid <> '' and b.unionid is not null " + " and a.cluebelongtocompany='")
                        .append(req.getUinfo().getCompanyId())
                        .append("' " + " and a.advertisementId='")
                        .append(param.get("activeTypeId"))
                        .append("' ");
                sb.append(") l");
            } else { // 发布
               if (StringUtils.isNotEmpty((String) param.get("activeType")) && "luodiye".equals((String) param.get("activeType"))) {
                   sb.delete(0, sb.length());
                   sb.append("select a.uuid id,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') image,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') thumb_image,a.companycontact name,a.cluephone phone " + " from clue a " + " left join active_user b on a.openid=b.openid and yhlb='02' " + " where a.isdelete=0  " + " and a.cluebelongtocompany='")
                           .append(req.getUinfo().getCompanyId())
                           .append("' " + " and a.advertisementId='")
                           .append(param.get("activeTypeId"))
                           .append("' ");
               } else {
                   sb.delete(0, sb.length());
                   sb.append("select a.uuid id,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') image,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') thumb_image,a.companycontact name,a.cluephone phone " + " from clue a " + " left join active_user b on a.openid=b.openid and yhlb='02' " + " where a.isdelete=0 and b.unionid <> '' and b.unionid is not null " + " and a.cluebelongtocompany='")
                           .append(req.getUinfo().getCompanyId())
                           .append("' " + " and a.advertisementId='")
                           .append(param.get("activeTypeId"))
                           .append("' ");
               }
            }
        }
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        for (int i = 0; i < list.size(); i++) {
            String imageString = (String) list.get(i).get("image");
            String ossImage = null;
            if (!StringUtils.isEmpty(imageString)) {
                ossImage = OssUtil.getPublicURL(imageString);
                list.get(i).put("image", ossImage);
            }
            final Random random = new Random();
            if (StringUtils.isEmpty(ossImage)) {
                int sj = random.nextInt(30) + 1;
                //头像名
                String imgUrl = "crm/avatars/cl" + sj + ".jpg";
                StringBuffer buffer = new StringBuffer();
                // buffer.append("update active_user set userPic = '").append(imgUrl).append("' where phone = '").append(map.get("phone")).append("'");
                // this.update(conn, buffer.toString(), new Object[]{});
                ossImage = OssUtil.getImgSignURL(imgUrl, 0);
                list.get(i).put("image", ossImage);
            }
        }
        map.put("list", list);
        map.put("allnumber", list.size());
        //获取奖品奖项列表
        sb.delete(0, sb.length());
        sb.append(" select a.uuid id,a.name,a.prize,a.prizeImg,a.chooseNumber choosenumber " + " from pc_active_reward a " + " where a.isdelete=0 and a.companyId='")
                .append(req.getUinfo().getCompanyId())
                .append("' " + " and a.activeId='")
                .append(param.get("uuid"))
                .append("' " + " order by a.serial asc ");
        List<Map<String, Object>> prizeList = this.queryForList(conn, sb.toString(), new Object[]{});
        if (prizeList.size() > 0) {
            for (int j = 0; j < prizeList.size(); j++) {
                String titleName = prizeList.get(j).get("prizeImg").toString();
                String titleUrl = OssUtil.getImgSignURL(titleName, 2);
                prizeList.get(j).put("url", titleUrl);
            }
        }
        map.put("prizeList", prizeList);
        res.setExtData(map);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getLuckUser(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取活动信息失败");
        Map<String, Object> extData = new HashMap();
        StringBuffer sb = new StringBuffer();
        //获取内定人员
        sb.delete(0, sb.length());
        sb.append(" select a.rewardId,a.clueId " + " from pc_active_lucky_user a " + " where a.isdelete=0 and a.companyId='")
                .append(req.getUinfo().getCompanyId())
                .append("' ");
        sb.append(" and a.activeId='").append(param.get("activeId")).append("' ");
        if (!StringUtils.isEmpty((String) param.get("rewardId"))) {
            sb.append(" and a.rewardId='").append(param.get("rewardId")).append("' ");
        }
        List<Map<String, Object>> luckyList = this.queryForList(conn, sb.toString(), new Object[]{});
        for (int i = 0; i < luckyList.size(); i++) {
            String rewardId = (String) luckyList.get(i).get("rewardId");
            String clueId = (String) luckyList.get(i).get("clueId");
            if (extData.containsKey(rewardId)) {
                List<String> obj = (List<String>) extData.get(rewardId);
                obj.add(clueId);
            } else {
                List<String> list = new ArrayList();
                list.add(clueId);
                extData.put(rewardId, list);
            }
        }
        res.setExtData(extData);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getWinList(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取中奖名单失败");
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        sb.append("select a.uuid,b.name rewardName,c.uuid userId,c.companycontact username,c.cluephone phone,a.createTime\n" + " from pc_active_award_record a\n" + " left join pc_active_reward b on a.rewardId=b.uuid\n" + " left join clue c on a.clueId=c.uuid\n" + " where a.isdelete=0 and a.activeId='")
                .append(param.get("activeId"))
                .append("' ");
        if (!StringUtils.isEmpty((String) param.get("name"))) {
            sb.append(" and (c.companycontact like '%")
                    .append(param.get("name"))
                    .append("%' or c.cluephone='")
                    .append(param.get("name"))
                    .append("') ");
        }
        sb.append(" order by a.createTime desc ");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getJoinUserList(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取参与用户失败");
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();

        if (!StringUtils.isEmpty((String) param.get("islook"))) {
            if ("look".equals(param.get("islook"))) { //预览
                if ("luodiye".equals((String) param.get("activeType"))) {
                    sb.append(" select l.id,l.image,l.thumb_image,l.`name`,l.phone,l.flag,l.serial,concat(left(l.phone,3),'****',right(l.phone,4)) tmPhone from ( "); //手机号脱敏 select concat(left(l.phone,3),'****',right(l.phone,4))
                    sb.append(" select a.uuid id,if(a.pic<>'' and a.pic is not null,a.pic,'') image,if(a.pic<>'' and a.pic is not null,a.pic,'') thumb_image,a.name,a.phone,2 flag,a.serial " + " from pc_active_default_user a " + " where a.isdelete=0 ");
                    sb.append(" union ");
                    sb.append(" select a.uuid id,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') image,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') thumb_image,a.companycontact name,a.cluephone phone,1 flag,a.serial " + " from clue a " + " left join active_user b on a.openid=b.openid " + " where a.isdelete=0 " + " and a.cluebelongtocompany='")
                            .append(param.get("companyId"))
                            .append("' " + " and a.advertisementId='")
                            .append(param.get("activeTypeId"))
                            .append("' ");
                    sb.append(") l where 1=1 ");
                    //根据uuid查询创建时间之后的用户信息
                    if (!StringUtils.isEmpty((String) param.get("uuid"))) {
                        sb.append(" and l.serial >=(select serial from clue where ")
                                .append("uuid='")
                                .append(param.get("uuid"))
                                .append("') and l.id!='")
                                .append(param.get("uuid"))
                                .append("'");
                    }
                    sb.append(" order by l.flag desc,l.serial asc ");
                } else  {
                    sb.append(" select l.id,l.image,l.thumb_image,l.`name`,l.phone,l.flag,l.serial,concat(left(l.phone,3),'****',right(l.phone,4)) tmPhone from ( "); //手机号脱敏 select concat(left(l.phone,3),'****',right(l.phone,4))
                    sb.append(" select a.uuid id,if(a.pic<>'' and a.pic is not null,a.pic,'') image,if(a.pic<>'' and a.pic is not null,a.pic,'') thumb_image,a.name,a.phone,2 flag,a.serial " + " from pc_active_default_user a " + " where a.isdelete=0 ");
                    sb.append(" union ");
                    sb.append(" select a.uuid id,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') image,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') thumb_image,a.companycontact name,a.cluephone phone,1 flag,a.serial " + " from clue a " + " left join active_user b on a.openid=b.openid " + " where a.isdelete=0 and b.unionid <> '' and b.unionid is not null " + " and a.cluebelongtocompany='")
                            .append(param.get("companyId"))
                            .append("' " + " and a.advertisementId='")
                            .append(param.get("activeTypeId"))
                            .append("' ");
                    sb.append(") l where 1=1 ");
                    //根据uuid查询创建时间之后的用户信息
                    if (!StringUtils.isEmpty((String) param.get("uuid"))) {
                        sb.append(" and l.serial >=(select serial from clue where ")
                                .append("uuid='")
                                .append(param.get("uuid"))
                                .append("') and l.id!='")
                                .append(param.get("uuid"))
                                .append("'");
                    }
                    sb.append(" order by l.flag desc,l.serial asc ");
                }
            } else { //发布
                if ("luodiye".equals((String) param.get("activeType"))) {
                    sb.append("select distinct a.uuid id,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') image,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') thumb_image,a.companycontact name,a.cluephone phone,concat(left(a.cluephone,3),'****',right(a.cluephone,4)) tmPhone " + " from clue a " + " left join active_user b on a.advertisementId = b.activityUuid and a.cluephone = b.phone" + " where a.isdelete=0 and a.cluebelongtocompany='")
                            .append(param.get("companyId"))
                            .append("' " + " and a.advertisementId='")
                            .append(param.get("activeTypeId"))
                            .append("' ");
                    //根据uuid查询创建时间之后的用户信息
                    if (!StringUtils.isEmpty((String) param.get("uuid"))) {
                        sb.append("and a.serial >=(select serial from clue where ")
                                .append("uuid='")
                                .append(param.get("uuid"))
                                .append("') and a.uuid!='")
                                .append(param.get("uuid"))
                                .append("'");
                    }
                    sb.append(" order by a.serial asc,a.modifyTime asc ");
                } else {
                    sb.append(" select a.uuid id,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') image,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') thumb_image,a.companycontact name,a.cluephone phone,concat(left(a.cluephone,3),'****',right(a.cluephone,4)) tmPhone " + " from clue a " + " left join active_user b on a.openid=b.openid" + " where a.isdelete=0 and b.unionid <> '' and b.unionid is not null  and a.cluebelongtocompany='")
                            .append(param.get("companyId"))
                            .append("' " + " and a.advertisementId='")
                            .append(param.get("activeTypeId"))
                            .append("' ");
                    //根据uuid查询创建时间之后的用户信息
                    if (!StringUtils.isEmpty((String) param.get("uuid"))) {
                        sb.append("and a.serial >=(select serial from clue where ")
                                .append("uuid='")
                                .append(param.get("uuid"))
                                .append("') and a.uuid!='")
                                .append(param.get("uuid"))
                                .append("'");
                    }
                    sb.append(" order by a.serial asc,a.modifyTime asc ");
                }
            }
        }
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        for (int i = 0; i < list.size(); i++) {
            String imageString = (String) list.get(i).get("image");
            String ossImage = null;
            if (StrUtil.isNotEmpty(imageString)) {
                ossImage = OssUtil.getPublicURL(imageString);
                list.get(i).put("image", ossImage);
            } else {
                final Random random = new Random();
                if (StringUtils.isEmpty(ossImage)) {
                    int sj = random.nextInt(30) + 1;
                    //头像名
                    String imgUrl = "crm/avatars/cl" + sj + ".jpg";
                    ossImage = OssUtil.getImgSignURL(imgUrl, 0);
                    list.get(i).put("image", ossImage);
                }
            }
        }
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData savePCAwardRecord(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存中奖记录失败");
        param.put("tableName", "pc_active_award_record");
        PageData sqlInfo = null;
        if (!StringUtils.isEmpty((String) param.get("islook")) && "look".equals(param.get("islook"))) {
            res.setRetCode(0);
            res.setRetMessage("OK");
            return res;
        }
        //根据activeId删除用户获奖记录
        StringBuffer sb = new StringBuffer();
        //sb.append("delete from pc_active_award_record where activeId='").append(param.get("activeId")).append("' and rewardId='").append(param.get("rewardId")).append("' ");
        sb.append(" UPDATE pc_active_award_record set isdelete=1 where activeId='")
                .append(param.get("activeId"))
                .append("' and rewardId='")
                .append(param.get("rewardId"))
                .append("' ");
        this.update(conn, sb.toString(), new Object[]{});
        param.put("companyId", req.getUinfo().getCompanyId());
        Object user1 = param.get("users");
        if (user1 instanceof String) {
            param.remove("uuid");
            param.put("clueId", user1);
            sqlInfo = getInsertSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.update(conn, sb_field.toString(), listData.toArray());
        } else {
            String[] users = (String[]) user1;
            for (int i = 0; i < users.length; i++) {
                param.remove("uuid");
                param.put("clueId", users[i]);
                sqlInfo = getInsertSQL(conn, req);
                if (sqlInfo.getRetCode() != 0) {
                    return sqlInfo;
                }
                final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
                final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
                this.update(conn, sb_field.toString(), listData.toArray());
            }
        }
        res.setKey((String) param.get("uuid"));
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getActivityList(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        //1 展会活动 2 抽奖活动
        sb.append("select l.uuid,l.name,l.type from (");
        sb.append("select a.uuid,concat('【落地页】',a.NAME) NAME,1 type,a.createTime from t_activity a where a.companyId='")
                .append(req.getUinfo().getCompanyId())
                .append("' ");
        sb.append(" union ");
        sb.append("select a.uuid,concat('【移动端】',a.activeName) NAME,2 type,a.createTime from active_session a where a.companyId='")
                .append(req.getUinfo().getCompanyId())
                .append("' ");
        sb.append(" union ");
        sb.append(" select p.uuid,concat('【海报页】',p.activeName) Name,3 type,p.createTime from poster p where p.isdelete<>1 and p.companyId='")
                .append(req.getUinfo().getCompanyId())
                .append("' ");
        sb.append(" ) l " + "  order by l.type,l.createTime desc,l.uuid asc  ");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getActiveJoinAndArea(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        Map<String, Object> extData = new HashMap();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        List<String> areas = new ArrayList<String>();
        List<Integer> counts = new ArrayList<Integer>();
        Map<String, Integer> map = new HashMap<String, Integer>();
        StringBuffer sb = new StringBuffer();
        String type = (String) param.get("type");
        if ("1".equals(type)) { //1 展会活动
            sb.delete(0, sb.length());
            sb.append("select a.uuid,if(b.xzqhjb<=2,b.xzqhdm,c.xzqhdm) xzqhdm,if(b.xzqhjb<=2,b.xzqhmc,c.xzqhmc) xzqhmc,a.cluephone,a.numberhome\n" + "from clue a\n" + "left join t_xzqh b on b.xzqhdm=a.xzqhdm " + " left join t_xzqh c on c.xzqhdm=concat(substr(b.xzqhdm,1,4),'00000000') and b.xzqhjb>2 " + "where a.isdelete=0 and a.cluebelongtocompany='")
                    .append(req.getUinfo().getCompanyId())
                    .append("' ");
            if (!StringUtils.isEmpty((String) param.get("activityId"))) {
                sb.append("and a.advertisementId='").append(param.get("activityId")).append("' ");
            }
        } else { // 2 抽奖活动
            sb.delete(0, sb.length());
            sb.append("select a.uuid,'86' xzqhdm,null xzqhmc,a.userPhone cluephone,a.numberhome\n" + "from active_record a " + "where a.companyId='")
                    .append(req.getUinfo().getCompanyId())
                    .append("' ");
            if (!StringUtils.isEmpty((String) param.get("activityId"))) {
                sb.append("and a.activeUuid='").append(param.get("activityId")).append("' ");
            }
            sb.append(" GROUP BY a.userphone ");
        }
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        for (int i = 0; i < list.size(); i++) {
            if (!"86".equals(list.get(i).get("xzqhdm"))) {
                Integer count = map.get(list.get(i).get("xzqhmc"));
                if (count != null) {
                    map.put((String) list.get(i).get("xzqhmc"), ++count);
                } else {
                    map.put((String) list.get(i).get("xzqhmc"), 1);
                }
            } else if (!StringUtils.isEmpty((String) list.get(i).get("numberhome"))) {
                String numberhome = (String) list.get(i).get("numberhome");
                String[] numberhomes = numberhome.split(" ");
                if (numberhomes.length > 1) {
                    String shi = numberhomes[1];
                    Integer count = map.get(shi);
                    if (count != null) {
                        map.put(shi, ++count);
                    } else {
                        map.put(shi, 1);
                    }
                } else {
                    String sheng = numberhomes[0];
                    Integer count = map.get(sheng);
                    if (count != null) {
                        map.put(sheng, ++count);
                    } else {
                        map.put(sheng, 1);
                    }
                }
            } else {
                String phone = (String) list.get(i).get("cluephone");
                //根据手机号获取归属地
                Map<String, String> obj = HttpUtils.getPhoneAddress(phone);
                String province = (String) obj.get("province");
                String city = (String) obj.get("city");
                //查询行政区划
                sb.delete(0, sb.length());
                sb.append("select xzqhdm,xzqhmc from t_xzqh where 1=1 ");
                if (!StringUtils.isEmpty(city)) {
                    sb.append(" and xzqhmc like '").append(city).append("%' and xzqhjb=2 ");
                } else {
                    sb.append(" and xzqhmc like '").append(province).append("%' and xzqhjb=1 ");
                }
                List<Map<String, Object>> xzqh = this.queryForList(conn, sb.toString(), new Object[]{});
                Integer count = map.get(xzqh.get(0).get("xzqhmc"));
                if (count != null) {
                    map.put((String) xzqh.get(0).get("xzqhmc"), ++count);
                } else {
                    map.put((String) xzqh.get(0).get("xzqhmc"), 1);
                }
            }
        }
        Set<Entry<String, Integer>> entrySet = map.entrySet();
        Iterator<Entry<String, Integer>> iterator = entrySet.iterator();
        while (iterator.hasNext()) {
            Entry<String, Integer> next = iterator.next();
            String key = next.getKey();
            Integer value = next.getValue();
            areas.add(key);
            counts.add(value);
        }
        extData.put("total", list.size());
        extData.put("areas", areas);
        extData.put("counts", counts);
        res.setExtData(extData);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getCouponUser(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取助力信息失败");
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        sb.append(" SELECT a.id,c.phone,b.max_help_count,b.`name` couponName,b.id couponId,b.price couponPrice,a.`status`,d.`name` statusName,format(a.help_result,2) help_result,help_count, " + " a.contact_name,a.contact_phone,a.contact_address " + " FROM coupon_user a" + " LEFT JOIN coupon b on a.coupon_id=b.id" + " LEFT JOIN `user` c on a.user_id=c.weixin_openid" + " LEFT JOIN t_param d on a.`status`=d.`value` and d.param_type='120'" + " WHERE c.phone <>'' and c.phone is not NULL ");
        if (!StringUtils.isEmpty((String) param.get("status"))) {
            sb.append(" and a.`status` ='").append(param.get("status")).append("' ");
        }
        if (!StringUtils.isEmpty((String) param.get("couponName"))) {
            sb.append(" and b.`name` like '%").append(param.get("couponName")).append("%' ");
        }
        sb.append(" order by a.update_time desc ");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" FROM ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getHelpUser(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取助力信息失败");

        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT b.phone content,DATE_FORMAT(a.add_time,'%Y-%m-%d %H:%m:%s') timestamp,'#0bbd87' color " + " FROM user_help a " + " LEFT JOIN user b on a.help_user_id=b.weixin_openid " + " WHERE a.coupon_user_id=? AND b.phone <>'' and b.phone is not NULL ");
        sb.append(" order by a.add_time desc ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{param.get("id")});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getUserInfoByOpenId(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        StringBuffer sb = new StringBuffer();
        sb.append(" select * from active_user where  openid = ? ");
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{param.get("openId")});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getActivityUser(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        sb.append(" select b.name activityName,a.activityUuid,a.uuid,a.companyId,a.name,a.phone,date_format(a.createTime,'%Y-%m-%d %H:%i:%s') createTime,a.jsondata " + " from active_user a " + " left join t_activity b on a.activityUuid=b.uuid " + " where a.yhlb='03' ");
        if (!StringUtils.isEmpty(req.getReqData().get("activityUuid").toString())) {
            sb.append(" and a.activityUuid = '").append(req.getReqData().get("activityUuid")).append("' ");
        }
        if (!StringUtils.isEmpty(req.getReqData().get("name").toString())) {
            sb.append(" and a.name like '%").append(req.getReqData().get("name")).append("%' ");
        }
        if (!StringUtils.isEmpty(req.getUinfo().getCompanyId())) {
            sb.append(" and a.companyId = '").append(req.getUinfo().getCompanyId()).append("' ");
        }
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});

        for (int i = 0; i < list.size(); i++) {
            //jsondata解码
            if (!StringUtils.isEmpty((String) list.get(i).get("jsondata"))) {
                String decode = URLDecoder.decode((String) list.get(i).get("jsondata"));
                list.get(0).put("jsondata", decode);
            }
        }
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getActivitySelect(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        sb.append(" select name,uuid value from t_activity where isdelete<>'1' ");
        if (!StringUtils.isEmpty(req.getUinfo().getCompanyId())) {
            sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ");
        }
        if (StringUtils.isNotEmpty((String) req.getReqData().get("cluesource"))) {
            sb.append(" and cluesource = '").append(req.getReqData().get("cluesource")).append("' ");
        }
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public List getParamData(Connection conn, String param_type, String value) throws SQLException {
        StringBuffer sb = new StringBuffer();
        sb.append(" select name,companyId from t_param where is_delete=0 and param_type=? and value =? ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{param_type, value});
        return list;
    }

    public PageData saveForwardRecord(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存转发记录失败");
        param.put("tableName", "forward_record");
        StringBuffer sb = new StringBuffer();
        String toUnionid = param.get("toUnionid").toString();
        String fromUnionid = param.get("fromUnionid").toString();

        //判断活动用户是否存在
        sb.delete(0, sb.length());
        sb.append("select count(1) cnt from active_user where unionid='").append(toUnionid).append("' ");
        int cnt = this.queryForInt(conn, sb.toString(), new Object[]{});
        if (cnt <= 0) {
            res.setRetCode(0);
            res.setRetMessage("OK");
            return res;
        }

        //判断关系是否保存过一次
        sb.delete(0, sb.length());
        sb.append("select count(1) cnt from forward_record_tree_path where activeId=? and ancestor=? and descendant=? ");
        int i = this.queryForInt(conn, sb.toString(), new Object[]{param.get("activeId"), fromUnionid, param.get("toUnionid")});
        if (i > 0) {
            res.setRetCode(0);
            res.setRetMessage("OK");
            return res;
        }

        //如果点击转发链接的openId==转发人的openId 不保存记录
        if (String.valueOf(fromUnionid).equals(param.get("toUnionid"))) {
            res.setRetCode(0);
            res.setRetMessage("OK");
            return res;
        }

        //保存转发记录
        param.put("unionid", fromUnionid);
        PageData sqlInfo = getInsertSQL(conn, req);
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());
        String uuid = (String) param.get("uuid");

        //关系表fromOpenId和toOpenId调换查一次是否存在
        sb.delete(0, sb.length());
        sb.append("select count(1) cnt from forward_record_tree_path where activeId=? and ancestor=? and descendant=? ");
        int i1 = this.queryForInt(conn, sb.toString(), new Object[]{param.get("activeId"), toUnionid, fromUnionid});
        if (i1 > 0) {
            res.setRetCode(0);
            res.setRetMessage("OK");
            return res;
        }

        //保存关系
        sb.delete(0, sb.length());
        sb.append("INSERT INTO forward_record_tree_path (ancestor, descendant,depth,forwardId,activeId)\n" + "SELECT t.ancestor, '" + toUnionid + "',t.depth+1,'" + uuid + "','" + param.get("activeId") + "' " + "FROM forward_record_tree_path AS t\n" + "WHERE t.activeId='" + param.get("activeId") + "' and t.descendant = '" + fromUnionid + "' and t.activeId='" + param.get("activeId") + "' " + "UNION \n" + "SELECT '" + fromUnionid + "','" + toUnionid + "',2,'" + uuid + "','" + param.get("activeId") + "'" + "UNION \n" + "SELECT '" + toUnionid + "','" + toUnionid + "',1,'" + uuid + "','" + param.get("activeId") + "'");
        this.update(conn, sb.toString(), new Object[]{});

        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getActiveSelect(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        sb.append(" select activeName name,uuid value from active_session where 1=1 ");
        if (!StringUtils.isEmpty(req.getUinfo().getCompanyId()) && !req.getUinfo().getCompanyId().equals("0000")) {
            sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ");
        }
        sb.append("  ORDER BY createTime desc  ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getWXActiveList(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        String domain_q = Globals.publicActiveAPI + "/static/choujiang/";
        String domain_h = "/index.html";
        //String domain = Globals.publicActiveAPI+"/#/g_choujiang";
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        sb.append("select l.id,l.uuid,l.companyId,l.companyName,l.activeName," + "date_format(l.activeTimeStart,'%Y-%m-%d %H:%i:%s') activeTimeStart,date_format(l.activeTimeEnd,'%Y-%m-%d %H:%i:%s') activeTimeEnd,l.url,l.type,if(l.phone is not null and l.phone!='',1,0) isJoin,l.indexTemplate,if(now()>=l.activeTimeStart and now()<=l.activeTimeEnd,'一键参与',(if(now()<l.activeTimeStart,'活动未开始','活动已结束'))) activeType from (\n" + "SELECT a.id,a.uuid, a.companyId,b.companyName, a.activeName,a.activeTimeStart,a.activeTimeEnd,1 type," + "concat('" + domain_q + "',a.indexTemplate,'" + domain_h + "','?uuid=',a.uuid,'&openId=','" + (!StringUtils.isEmpty((String) param.get("openId")) ? param.get("openId") : "") + "','&indexTemplate=',a.indexTemplate) url,c.userPhone phone,a.indexTemplate \n" + "FROM active_session a\n" + "left join t_company b on a.companyId=b.companyId\n" + "left join active_record c on a.uuid=c.activeUuid and c.userPhone='" + param.get("phone") + "'" + "where a.sffb='1' and a.companyId!='模板' \n" + "group by a.uuid " + "union \n" + "select a.id,a.uuid,a.companyId,b.companyName,a.activeName,a.activeTimeStart,a.activeTimeEnd,2 type,a.url,c.cluephone phone,null indexTemplate \n" + "from active_custom a\n" + "left join t_company b on a.companyId=b.companyId\n" + "left join clue c on a.uuid=c.advertisementId and c.cluephone='" + param.get("phone") + "'" + ") l where 1=1");
        //			if(!StringUtils.isEmpty((String) param.get("companyId"))) {
        //				sb.append(" and l.companyId='").append(param.get("companyId")).append("' ");
        //			}
        if (!StringUtils.isEmpty((String) param.get("id"))) {
            sb.append(" and l.id='").append(param.get("id")).append("' ");
        }
        if (!StringUtils.isEmpty((String) param.get("activeId"))) {
            sb.append(" and l.uuid='").append(param.get("activeId")).append("' ");
        }
        String indexTemplate = String.valueOf(param.get("indexTemplate"));
        if (!StringUtils.isEmpty(indexTemplate) && indexTemplate != "null") {
            sb.append(" and l.indexTemplate='").append(param.get("indexTemplate")).append("' ");
        }
        sb.append(" order by l.activeTimeStart desc,l.uuid ");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});

        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getForwardRecordForUser(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        String openId = (String) param.get("openId");
        String activeId = (String) param.get("activeId");

        List<Map<String, Object>> list = getForwardRecordList(conn, openId, activeId);
        res.setExtData(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    private List<Map<String, Object>> getForwardRecordList(Connection conn, String openId,
            String activeId) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        StringBuffer sb = new StringBuffer();

        sb.append("select a.*\n" + "from forward_record_tree_path a\n" + "left join forward_record b on a.forwardId=b.uuid\n" + "where a.ancestor=? and a.depth=2  and a.activeId=? ");
        list = this.queryForList(conn, sb.toString(), new Object[]{openId, activeId});
        //			for(int i=0;i<list.size();i++) {
        //				openId = (String) list.get(i).get("descendant");
        //				list.get(i).put("child", this.getForwardRecordList(openId, activeId));
        //			}

        return list;
    }

    public PageData getForwardUser(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        StringBuffer sb = new StringBuffer();

        sb.append(" select * from active_record where activeUuid = ? ");
        sb.append(" group by userUuid,activeUuid order by userUuid ");
        list = this.queryForList(conn, sb.toString(), new Object[]{(String) param.get("activeUuid")});
        res.setExtData(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData okCashPrize(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("兑奖失败");

        String uuid = (String) param.get("uuid");
        if (!StringUtils.isEmpty(uuid)) {
            StringBuffer sb = new StringBuffer();
            sb.append(" update active_record set prizeType='02' where uuid='").append(uuid).append("' ");
            this.update(conn, sb.toString(), new Object[]{});
            res.setRetCode(0);
            res.setRetMessage("OK");
        } else {
            return res;
        }

        return res;
    }

    public PageData getActiveUserAddressList(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询活动用户地址失败");
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();

        sb.append("select a.uuid,replace(a.xzqh,'->',' ') xzqh,a.address,a.name,a.phone,a.isDefault\n" + " from active_user_address a\n" + "where a.isdelete=0 ");
        if (!StringUtils.isEmpty((String) param.get("userUuid"))) {
            sb.append(" and a.activeUserId='" + param.get("userUuid") + "' ");
        }
        if (!StringUtils.isEmpty((String) param.get("isDefault"))) {
            sb.append(" and a.isDefault='").append(param.get("isDefault")).append("' ");
        }
        if (!StringUtils.isEmpty((String) param.get("uuid"))) {
            sb.append(" and a.uuid='").append(param.get("uuid")).append("' ");
        }
        sb.append("order by a.createTime asc");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getActiveUserAddressDetail(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询活动用户地址失败");
        StringBuffer sb = new StringBuffer();

        sb.append("select a.uuid,a.xzqh,a.address,a.name,a.phone,a.isDefault\n" + " from active_user_address a\n" + "where a.isdelete=0 ");
        sb.append(" and a.uuid='").append(param.get("uuid")).append("' ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setExtData(list.get(0));
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData saveActiveUserAddress(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "active_user_address");
        StringBuffer sb = new StringBuffer();

        if (!StringUtils.isEmpty((String) param.get("isDefault"))) {
            if ("1".equals(param.get("isDefault"))) {
                //清空活动用户默认地址
                sb.delete(0, sb.length());
                sb.append("update active_user_address set isDefault='0' where activeUserId='")
                        .append(param.get("activeUserId"))
                        .append("' ");
                int update = this.update(conn, sb.toString(), new Object[]{});
            } else { //入参默认为0
                //查询是否有默认地址 没有的话将这个地址设为默认地址
                //					sb.delete(0, sb.length());
                //					sb.append("select count(1) cnt from active_user_address where activeUserId='").append(param.get("activeUserId")).append("' ");
                //					int cnt = this.getJdbcTemplate_newg().queryForInt(sb.toString());
                //					if(cnt<=0) {
                //						param.put("isDefault", "1");
                //					}
            }

        }
        String uuid = (String) param.get("uuid");
        if (StringUtils.isEmpty(uuid)) {
            PageData sqlInfo = getInsertSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            int i = this.update(conn, sb_field.toString(), listData.toArray());
            if (i <= 0) {
                res.setRetCode(9);
                res.setRetMessage("保存失败! ");
                return res;
            }
        } else {
            param.put("keys", "uuid");
            PageData sqlInfo = getUpdateSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            int i = this.update(conn, sb_field.toString(), listData.toArray());
            if (i <= 0) {

                res.setRetCode(9);
                res.setRetMessage("保存失败! ");
                return res;
            }
        }


        res.setRetCode(0);
        res.setRetMessage("OK");
        res.setKey((String) param.get("uuid"));

        return res;
    }

    public PageData delActiveUserAddress(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        param.put("tableName", "active_user_address");

        param.put("isdelete", "1");
        param.put("keys", "uuid");
        PageData sqlInfo = getUpdateSQL(conn, req);
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        int i = this.update(conn, sb_field.toString(), listData.toArray());

        if (i <= 0) {
            res.setRetCode(9);
            res.setRetMessage("保存失败! ");
        }
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getActiveUserDefaultAddress(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();

        sb.append("select * from (\n" + "select a.uuid,a.xzqh,a.address,a.name,a.phone \n" + "from active_user_address a \n" + " where a.isdelete='0' and a.isDefault='1' and a.activeUserId='" + param.get("userUuid") + "'\n" + " union \n" + "(select a.uuid,a.xzqh,a.address,a.name,a.phone \n" + "from active_user_address a \n" + " where a.isdelete='0'  and a.activeUserId='" + param.get("userUuid") + "' order by a.createTime limit 1)\n" + " ) l limit 1");
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setExtData(list.get(0));

        return res;
    }

    public PageData savePCActiveJoinUser(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();

        String nickName = (String) param.get("nickName");//用户名
        String phone = (String) param.get("phone"); //电话
        String avatarUrl = (String) param.get("avatarUrl"); //头像
        String openid = (String) param.get("openid"); //openid

        Map<String, String> phoneAddress = HttpUtils.getPhoneAddress(phone);
        String numberhome = phoneAddress.get("province") + " " + phoneAddress.get("city"); //号码归属地
        //查询是否有活动
        sb.delete(0, sb.length());
        sb.append("select uuid,companyId from t_activity where sfmr=1 limit 1");
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        if (list.size() <= 0) {
            res.setRetCode(9);
            res.setRetMessage("暂无相关活动! ");
            return res;
        }
        String activeId = (String) list.get(0).get("uuid"); //展会活动ID
        String companyId = (String) list.get(0).get("companyId"); //展会所属公司
        //查询是否参加过活动
        sb.delete(0, sb.length());
        sb.append("select count(1) cnt from clue where cluephone='")
                .append(phone)
                .append("' and advertisementId='")
                .append(activeId)
                .append("' ");
        int i = this.queryForInt(conn, sb.toString(), new Object[]{});
        if (i > 0) {
            res.setRetCode(9);
            res.setRetMessage("已参加该活动,请等待抽奖! ");
            return res;
        }
        //保存参加现场抽奖活动用户
        sb.delete(0, sb.length());
        UUID uuid = UUID.randomUUID();
        sb.append("insert into clue(uuid,cluestage,followupstatus,poolentrymode,cluesource,advertisementId,culestate,companycontact,cluephone,xzqhdm,numberhome,openid,cluebelongtocompany,isdelete) ")
                .append(" values('" + uuid + "','01','01','01','07','" + activeId + "','01','" + nickName + "','" + phone + "','86','" + numberhome + "','" + openid + "','" + companyId + "','0' ) ");
        int result = this.update(conn, sb.toString(), new Object[]{});
        if (result <= 0) {
            res.setRetCode(9);
            res.setRetMessage("保存失败! ");
            return res;
        }
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getActiveDetails(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        Map<String, Object> extData = new LinkedHashMap<String, Object>();
        String openId = (String) param.get("openId");
        String unionid = (String) param.get("unionid");
        String activeId = (String) param.get("activeId");
        String userUuid = "";
        StringBuffer sb = new StringBuffer();
        //活动基本信息 getActiveSessionApp
        sb.append(" select * from active_session where ").append(" uuid= '").append(activeId).append("' ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        if (list.size() > 0) {
            for (int j = 0; j < list.size(); j++) {
                //头部图片
                String StringSession = list.get(j).get("mineFormMesg").toString();
                JSONObject jsonObject = (JSONObject) JSON.parse(StringSession);
                Map<String, Object> map = JSONObject.parseObject(jsonObject.toJSONString(), Map.class);
                String titleName = list.get(0).get("titleUrl").toString();
                String titleUrl = OssUtil.getImgSignURL(titleName, 0);
                Map<String, String> titleImg = new HashMap<String, String>();
                titleImg.put("name", titleName);
                titleImg.put("url", titleUrl);
                ArrayList<Object> titleUrlMap = new ArrayList<Object>();
                titleUrlMap.add(0, titleImg);
                //背景图片
                String backgroundName = list.get(0).get("backgroundUrl").toString();
                String backgroundUrl = OssUtil.getImgSignURL(backgroundName, 0);
                Map<String, String> backgroundImg = new HashMap<String, String>();
                backgroundImg.put("name", backgroundName);
                backgroundImg.put("url", backgroundUrl);
                ArrayList<Object> backgroundUrlMap = new ArrayList<Object>();
                backgroundUrlMap.add(0, backgroundImg);
                //奖品信息
                List<Map<String, Object>> jplist = (List<Map<String, Object>>) map.get("jplist");
                for (int i = 0; i < jplist.size(); i++) {
                    String images = jplist.get(i).get("jpUrl").toString();
                    if (!StringUtils.isEmpty(images)) {
                        String[] imageList = images.split(";", -1);
                        List<String> imgURL = new ArrayList<String>();
                        for (int k = 0; k < imageList.length; k++) {
                            String str = imageList[k];
                            imgURL.add(OssUtil.getImgSignURL(str, 0));
                        }
                        Map<String, String> jpImg = new HashMap<String, String>();
                        jpImg.put("name", imageList[0]);
                        jpImg.put("url", imgURL.get(0));
                        ArrayList<Object> jpUrlMap = new ArrayList<Object>();
                        jpUrlMap.add(0, jpImg);
                        jplist.get(i).put("jpUrlMap", jpUrlMap);
                    }
                }
                list.get(j).put("titleUrlMap", titleUrlMap);
                list.get(j).put("backgroundUrlMap", backgroundUrlMap);
                list.get(j).put("jplist", jplist);
            }
        } else {
            res.setRetCode(9);
            res.setRetMessage("没有查询到该活动");
            return res;
        }
        extData.put("activeInfo", list.get(0));
        BigDecimal forward = new BigDecimal((String) list.get(0).get("forward"));//每转发几次增加抽奖次数
        BigDecimal maxForward = new BigDecimal((String) list.get(0).get("maxForward"));//最大增加抽奖次数
        BigDecimal activeCjcs = new BigDecimal((String) list.get(0).get("activeCjcs"));//活动抽奖次数

        //参与用户信息 getUserInfoByUnionid
        sb.delete(0, sb.length());
        sb.append(" select * from active_user where unionid = ? or openid = ?");
        List<Map<String, Object>> list_userInfo = this.queryForList(conn, sb.toString(), new Object[]{unionid, openId});
        if (list_userInfo.size() > 0) {
            extData.put("userInfo", list_userInfo.get(0));
            if (StringUtils.isEmpty(userUuid)) {
                userUuid = (String) list_userInfo.get(0).get("uuid");
            }
        }

        //转发信息 getForwardRecordForUser
        sb.delete(0, sb.length());
        sb.append("select count(1) " + "from forward_record_tree_path a\n" + "left join forward_record b on a.forwardId=b.uuid\n" + "where a.ancestor=? and a.depth=2 and a.activeId=? ");
        int cht = this.queryForInt(conn, sb.toString(), new Object[]{unionid, activeId});
        extData.put("forwardCnt", cht);
        BigDecimal zfcs = new BigDecimal(String.valueOf(cht));//助力人数

        //参与用户 getForwardUser
        sb.delete(0, sb.length());
        sb.append(" select count(distinct(userUuid)) from active_record where activeUuid = ? ");
        int cnt = this.queryForInt(conn, sb.toString(), new Object[]{activeId});
        extData.put("forwardUserCnt", cnt);

        //用户抽奖次数 getUserCjcs
        sb.delete(0, sb.length());
        sb.append(" select count(1) userCjcs from active_record where userUuid =? and activeUuid=? ");
        int Cjcs = this.queryForInt(conn, sb.toString(), new Object[]{userUuid, activeId});
        extData.put("userCjcs", Cjcs);
        BigDecimal userCjcs = new BigDecimal(String.valueOf(Cjcs));

        //每转发几次增加抽奖次数  forward
        //最大增加抽奖次数 maxForward
        //活动抽奖次数 activeCjcs
        //助力人数  zfcs
        //用户已抽奖次数 userCjcs
        //计算转发增加次数
        BigDecimal addCnt;
        if (zfcs.equals(new BigDecimal(0)) || forward.equals(new BigDecimal(0))) {
            addCnt = new BigDecimal(0);
        } else {
            addCnt = zfcs.divide(forward, 0, BigDecimal.ROUND_DOWN);
        }
        if (addCnt.intValue() > maxForward.intValue()) {
            addCnt = maxForward;
        }

        //查询填写资料的抽奖次数
        sb.delete(0, sb.length());
        sb.append(" select count(1) customerCnt from `customer` where openId =? and activeUuid=? and ( businessdirection is not null and businessdirection<>'') ");
        int customerCnt = this.queryForInt(conn, sb.toString(), new Object[]{openId, activeId});
        extData.put("perfectInformation", 0);
        if (customerCnt > 0) {
            //activeCjcs = activeCjcs.add(maxForward) ;	--去掉填资料增加最大次数逻辑
            extData.put("perfectInformation", 1);
        }

        BigDecimal leftCnt = addCnt.add(activeCjcs).subtract(userCjcs);
        extData.put("leftCnt", leftCnt.intValue());
        if (leftCnt.intValue() < 0) {
            extData.put("leftCnt", 0);
        }

        res.setExtData(extData);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getActiveLeftCnt(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        String activeId = (String) param.get("activeUuid");
        String userUuid = (String) param.get("userUuid");
        String unionid = (String) param.get("unionid");
        String openId = (String) param.get("openId");
        StringBuffer sb = new StringBuffer();
        //活动基本信息 getActiveSessionApp
        sb.append(" select forward,maxForward,activeCjcs from active_session where ")
                .append(" uuid= '")
                .append(activeId)
                .append("' ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        if (list.size() == 0) {
            res.setRetCode(9);
            res.setRetMessage("没有查询到该活动！");
            return res;
        }
        BigDecimal forward = new BigDecimal((String) list.get(0).get("forward"));  //每转发几次增加抽奖次数
        BigDecimal maxForward = new BigDecimal((String) list.get(0).get("maxForward"));//最大增加抽奖次数
        BigDecimal activeCjcs = new BigDecimal((String) list.get(0).get("activeCjcs"));//活动抽奖次数

        //转发信息  getForwardRecordForUser
        sb.delete(0, sb.length());
        sb.append("select count(1) " + "from forward_record_tree_path a\n" + "left join forward_record b on a.forwardId=b.uuid\n" + "where a.ancestor=? and a.depth=2  and a.activeId=? ");
        int cht = this.queryForInt(conn, sb.toString(), new Object[]{unionid, activeId});
        BigDecimal zfcs = new BigDecimal(String.valueOf(cht));//助力人数

        //用户抽奖次数
        sb.delete(0, sb.length());
        sb.append(" select count(1) userCjcs from active_record where userUuid =? and activeUuid=? ");
        int Cjcs = this.queryForInt(conn, sb.toString(), new Object[]{userUuid, activeId});
        BigDecimal userCjcs = new BigDecimal(String.valueOf(Cjcs));
        //每转发几次增加抽奖次数  forward  最大增加抽奖次数 maxForward  活动抽奖次数 activeCjcs  助力人数  zfcs 用户已抽奖次数 userCjcs
        //计算转发增加次数
        BigDecimal addCnt = zfcs.divide(forward, 0, BigDecimal.ROUND_DOWN);
        if (addCnt.intValue() > maxForward.intValue()) {
            addCnt = maxForward;
        }

        //查询填写资料的抽奖次数
        sb.delete(0, sb.length());
        sb.append(" select count(1) customerCnt from `customer` where openId =? and activeUuid=? ");
        int customerCnt = this.queryForInt(conn, sb.toString(), new Object[]{openId, activeId});
        if (customerCnt > 0) {
            activeCjcs = activeCjcs.add(maxForward);
        }
        BigDecimal leftCnt = addCnt.add(activeCjcs).subtract(userCjcs);
        if (leftCnt.intValue() < 0) {
            leftCnt = new BigDecimal("0");
        }
        res.setExtData(leftCnt.intValue());
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    @SuppressWarnings({"unchecked"})
    public PageData getCustomActiveList(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        sb.append("select a.indextemplate,a.uuid,a.activeName,a.activePic,a.activeTimeStart,a.activeTimeEnd,a.url\n" + " from active_custom a\n" + "	where a.isdelete=0 ");
        if (!StringUtils.isEmpty((String) param.get("companyId"))) {
            sb.append(" a.companyId='").append(param.get("companyId")).append("' ");
        }

        sb.append(" order by a.activeTimeEnd desc ");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});

        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    public PageData getZiliaoParam(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        Object extData[] = new Object[1];

        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT value,name from t_param where param_type='130' ");
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        Map<String, Object> ywfx = new HashMap();
        ywfx.put("name", "您的业务方向");
        ywfx.put("list", list);
        ywfx.put("type", "2");
        extData[0] = ywfx;

        //			sb.append(" SELECT value,name from t_param where param_type='127' ");
        //			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
        //			Map<String,Object> ywfx = new HashMap();
        //			ywfx.put("name", "单位业务方向");
        //			ywfx.put("list", list);
        //			ywfx.put("type", "2");
        //			extData[0]=ywfx;
        //
        //			sb.delete(0, sb.length());
        //			sb.append(" SELECT value,name from t_param where param_type='128' ");
        //			list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
        //			Map<String,Object> lxrzw = new HashMap();
        //			lxrzw.put("name", "我的岗位");
        //			lxrzw.put("list", list);
        //			lxrzw.put("type", "1");
        //			extData[1]=lxrzw;
        //
        //			sb.delete(0, sb.length());
        //			sb.append(" SELECT value,name from t_param where param_type='129' ");
        //			list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
        //			Map<String,Object> qysf = new HashMap();
        //			qysf.put("name", "我关注的产品主要用于");
        //			qysf.put("list", list);
        //			qysf.put("type", "1");
        //			extData[2]=qysf;

        res.setExtData(extData);
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData saveCustomer(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        //先保存线索信息
        param.put("tableName", "clue");
        //根据用户信息查询是否已有客户信息
        StringBuffer sb = new StringBuffer();
        sb.append(" select uuid from clue where advertisementId=? and openId=? ");
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{param.get("activeUuid"), param.get("openId")});
        String uuid = "";
        if (list.size() > 0) {
            uuid = (String) list.get(0).get("uuid");
        }
        param.put("uuid", uuid);
        if (StringUtils.isEmpty(uuid)) {
            PageData sqlInfo = getInsertSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.update(conn, sb_field.toString(), listData.toArray());
        } else {
            param.put("keys", "uuid");
            PageData sqlInfo = getUpdateSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.update(conn, sb_field.toString(), listData.toArray());
        }

        //再保存客户信息
        param.put("tableName", "customer");
        //根据用户信息查询是否已有客户信息
        sb = new StringBuffer();
        sb.append(" select uuid from customer where activeUuid=? and openId=? ");
        list = this.queryForList(conn, sb.toString(), new Object[]{param.get("activeUuid"), param.get("openId")});
        if (list.size() > 0) {
            uuid = (String) list.get(0).get("uuid");
        }
        param.put("uuid", uuid);
        if (StringUtils.isEmpty(uuid)) {
            PageData sqlInfo = getInsertSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.update(conn, sb_field.toString(), listData.toArray());
        } else {
            param.put("keys", "uuid");
            PageData sqlInfo = getUpdateSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.update(conn, sb_field.toString(), listData.toArray());
        }

        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getShopList(Connection conn, Connection conn_shop, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        //查询商城公司店铺id
        if (!StringUtils.isEmpty(req.getUinfo().getCompanyId())) {
            StringBuffer sb = new StringBuffer();
            sb.append(" SELECT mer_id from t_company where 1=1");
            sb.append(" and companyId = '").append(req.getUinfo().getCompanyId()).append("' ");
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> list1 = this.queryForList(conn, sb.toString(), new Object[]{});
            //商城店铺id
            String mer_id = (String) list1.get(0).get("mer_id");

            sb = new StringBuffer();
            sb.append(" SELECT id `value`,name `name` from eb_coupon where 1=1 and is_del=0 and status=1 ");
            if (!StringUtils.isEmpty(mer_id) && !mer_id.equals("0")) {
                sb.append(" and mer_id = '").append(mer_id).append("'");
            }
            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> list2 = this.queryForList(conn_shop, sb.toString(), new Object[]{});
            res.setPage(list2);
        }
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData savePCActiveJoinUser2023(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        StringBuffer sb = new StringBuffer();

        String companyId = (String) param.get("companyId");//活动创建公司
        //			if(StringUtils.isEmpty(companyId)) {
        //				companyId = "0045" ;
        //			}
        String cluecompany = (String) param.get("cluecompany"); //填写的公司
        String companycontact = (String) param.get("companycontact"); //公司联系人
        String businessdirection = (String) param.get("businessdirection"); //业务方向
        String phone = (String) param.get("phone"); //电话
        String sex = (String) param.get("sex");//性别
        Map<String, String> phoneAddress = HttpUtils.getPhoneAddress(phone);
        String numberhome = phoneAddress.get("province") + " " + phoneAddress.get("city"); //号码归属地
        String openId = (String) param.get("openId");//性别

        //查询是否有活动
        sb.delete(0, sb.length());
        sb.append("select uuid,cluesource,createUid from t_activity where sfmr=1 and companyId=? and starttime<now() and endtime>now()  ORDER BY modifyTime desc limit 1  ");
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{companyId});
        if (list.size() <= 0) {
            res.setRetCode(9);
            res.setRetMessage("暂无相关活动或者活动已结束! ");
            return res;
        }

        String activeId = (String) list.get(0).get("uuid"); //展会活动ID
        String cluesource = (String) list.get(0).get("cluesource"); //展会所属公司
        String createUid = (String) list.get(0).get("createUid");

        //查询是否参加过活动
        sb.delete(0, sb.length());
        sb.append("select count(1) cnt from clue where openId=? and advertisementId=? ");
        int i = this.queryForInt(conn, sb.toString(), new Object[]{openId, activeId});
        if (i > 0) {
            res.setRetCode(1);
            res.setRetMessage("已参加该活动,请等待抽奖! ");
            return res;
        }

        if (StringUtils.isEmpty(phone) || StringUtils.isEmpty(cluecompany)) {
            res.setRetCode(2);
            res.setRetMessage("参数不完整! ");
            return res;
        }

        //保存参加现场抽奖活动用户
        sb.delete(0, sb.length());
        UUID uuid = UUID.randomUUID();
        sb.append("insert into clue(uuid,cluestage,followupstatus,poolentrymode,cluesource,advertisementId,culestate,cluecompany,companycontact,pooltype,sex,cluephone,xzqhdm,numberhome,openid,cluebelongtocompany,isdelete,createUid,modifyUid,modifyTime) ")
                .append(" values('" + uuid + "',?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now()) ");
        int result = this.update(conn, sb.toString(), new Object[]{"01", "01", "01", cluesource, activeId, null, cluecompany, companycontact, businessdirection, sex, phone, "86", numberhome, openId, companyId, "0", createUid, createUid});
        if (result <= 0) {
            res.setRetCode(9);
            res.setRetMessage("保存失败! ");
            return res;
        }
        res.setRetCode(0);
        res.setRetMessage("OK");

        return res;
    }

    public PageData getProductIdByCouponId(Connection conn, Connection conn_shop, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        StringBuffer sb = new StringBuffer();
        String couponId = (String) req.getReqData().get("couponId");//优惠卷id
        sb = new StringBuffer();
        sb.append(" SELECT c.name store_name,c.id productId,a.id couponId,a.`name` couponName,a.category,a.mer_id merId FROM eb_coupon a \n" + "LEFT JOIN eb_coupon_product b on a.id=b.cid \n" + "LEFT JOIN eb_product c on b.pid=c.id \n" + "where 1=1 ");
        sb.append(" and a.id = '").append(couponId).append("'");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn_shop, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    //	public static void main(String[] args) throws SQLException {
    //
    ////		int i = (int)(0.999999999999*100);
    ////		System.out.println(i);
    //
    //		Connection conn = null ;
    //		try {
    //			DataReq req = new DataReq();
    //			req.getReqData().put("uuid", "24883ca4-8b8a-4cf5-92b8-38ded023b5d3") ;
    //			ActiveDaoImpl l = new ActiveDaoImpl();
    //			conn = DBUtils.getJdbcForDB();
    //			PageData res = l.getJpAndSaveRecord( conn,req) ;
    //			System.out.println(res.getKey());
    //		}finally {
    //			conn.close();
    //		}
    //	}

    public PageData getJpAndSaveRecord(Connection conn, Connection conn_mall, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("抱歉，奖品已抽完");
        StringBuffer sb = new StringBuffer();
        String activeUuid = (String) req.getReqData().get("activeUuid");
        String userUuid = (String) req.getReqData().get("userUuid");
        sb.append("SELECT activeCjcs,mineFormMesg from active_session where activeTimeStart<=now() and activeTimeEnd>=now()  and uuid=? ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{activeUuid});
        if (list.size() == 0) {
            res.setRetCode(9);
            res.setRetMessage("没有查询到该活动或该活动已过期！");
            return res;
        }
        List<String> jiangpinchi = new ArrayList<String>();
        Map<String, Object> jiangpin = new LinkedHashMap<String, Object>();
        String fjpName = "谢谢参与";
        JSONArray jplist = null;
        for (Map<String, Object> map : list) {
            String mineFormMesg = (String) map.get("mineFormMesg");
            JSONObject obj = JSONObject.parseObject(mineFormMesg);
            jplist = obj.getJSONArray("jplist");
            for (int i = 0; i < jplist.size(); i++) {
                Map<String, Object> e = (Map<String, Object>) jplist.get(i);
                BigDecimal jpgl = new BigDecimal(e.get("jpgl").toString());
                //int num = jpgl.multiply(new BigDecimal("100")).intValue() ;
                int num = jpgl.intValue();//去掉乘以100
                String jpname = (String) e.get("jpname");
                String sfwjp = (String) e.get("sfwjp");
                for (int k = 0; k < num; k++) {
                    jiangpinchi.add((String) e.get("jpname"));
                }
                //计算每种奖品共有多少个
                if ("是".equals(sfwjp)) {
                    String jpsl = e.get("jpsl").toString();
                    if (jiangpin.get(jpname) == null) {
                        jiangpin.put(jpname, jpsl);
                    } else {
                        jiangpin.put(jpname, ((new BigDecimal((String) jiangpin.get(jpname))).add(new BigDecimal(jpsl))).toString());
                    }
                } else {
                    fjpName = jpname;
                }

            }
        }
        //去掉设置奖品数量为0的奖品
        for (String key : jiangpin.keySet()) {
            String cnt = (String) jiangpin.get(key);
            if (Double.parseDouble(cnt) == 0) { //如果本身奖品数量为0
                for (int j = 0; j < jiangpinchi.size(); j++) {
                    if (jiangpinchi.get(j).equals(key)) {
                        jiangpinchi.set(j, fjpName);
                    }
                }
            }
        }
        //根据抽奖记录去掉已经抽完的奖品
        sb.delete(0, sb.length());
        sb.append("SELECT prizeName,count(1) cnt from active_record where activeUuid=? and sfwjp='是' GROUP BY prizeName ");
        list = this.queryForList(conn, sb.toString(), new Object[]{activeUuid});
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> e = list.get(i);
            String prizeName = (String) e.get("prizeName");
            String cnt = (String) e.get("cnt").toString();
            String shezhicnt = (String) jiangpin.get(prizeName);
            if (shezhicnt != null && (Integer.parseInt(shezhicnt) <= Integer.parseInt(cnt))) {//该奖品已抽完
                for (int j = 0; j < jiangpinchi.size(); j++) {
                    if (jiangpinchi.get(j).equals(prizeName)) {
                        jiangpinchi.set(j, fjpName);
                    }
                }
            }
        }
        Collections.shuffle(jiangpinchi);
        int i = (int) (Math.random() * 100);
        String jiangpinname = jiangpinchi.get(i);
        //查询填写资料的抽奖次数
        //		sb.delete(0, sb.length()) ;
        //		sb.append(" select count(1) customerCnt from `customer` where openId =(select openId from active_user where uuid=?) and activeUuid=? and ( businessdirection is not null and businessdirection<>'') ") ;
        //		int customerCnt=this.queryForInt(conn,sb.toString(),new Object[] {userUuid,activeUuid});
        //		if(customerCnt>0) {//如果是填资料得到的机会，可以不做判断
        //			//判断用户是否已经中过两个奖
        //			sb.delete(0, sb.length()) ;
        //			sb.append("SELECT count(1) cnt from active_record where activeUuid=? and userUuid = ? and sfwjp='是' ") ;
        //			int cnt1 = this.queryForInt(conn,sb.toString(), new Object[]{activeUuid,userUuid}) ;
        //			if(cnt1==2) {
        //				jiangpinname = fjpName ;
        //			}
        //		}else{
        //第一次抽奖一定抽不中
        sb.delete(0, sb.length());
        sb.append("SELECT count(1) cnt from active_record where activeUuid=? and userUuid = ? ");
        int cnt2 = this.queryForInt(conn, sb.toString(), new Object[]{activeUuid, userUuid});
        if (cnt2 == 0) {
            jiangpinname = fjpName;
        } else {
            //判断用户是否已经中过奖
            sb.delete(0, sb.length());
            sb.append("SELECT count(1) cnt from active_record where activeUuid=? and userUuid = ? and sfwjp='是' ");
            int cnt1 = this.queryForInt(conn, sb.toString(), new Object[]{activeUuid, userUuid});
            if (cnt1 > 0) {
                jiangpinname = fjpName;
            }
        }
        //		}
        res.setKey("0");
        for (int m = 0; m < jplist.size(); m++) {
            Map<String, Object> e = (Map<String, Object>) jplist.get(m);
            String jpname = (String) e.get("jpname");
            if (jpname.equals(jiangpinname)) {
                res.setKey(String.valueOf(m));
                break;
            }
        }

        //保存中奖记录
        int num = Integer.parseInt(res.getKey());
        Map<String, Object> param = req.getReqData();
        Map<String, Object> mapPrize = (Map<String, Object>) jplist.get(num);
        param.put("prizeName", mapPrize.get("jpname"));
        param.put("prizeRule", mapPrize.get("prizeRule"));
        param.put("sfwjp", mapPrize.get("sfwjp"));
        param.put("isShoppingMalls", mapPrize.get("isShoppingMalls"));
        param.put("couponId", mapPrize.get("couponId"));
        param.put("couponName", mapPrize.get("couponName"));
        //如果是商城优惠卷，插入商城优惠卷
        String couponUserId = "";
        if (mapPrize.get("isShoppingMalls").equals("是") && !mapPrize.get("couponId")
                .equals("") && mapPrize.get("couponId") != null) {
            String shopkey = req.getReqData().get("shopkey").toString();
            String url = Globals.SHOP_COUPONRECEIVE_URL + "/" + mapPrize.get("couponId").toString();
            Map<String, String> header = new HashMap<String, String>();
            header.put("Authori-Zation", shopkey);
            byte[] body = "".getBytes();
            String sendPost = HttpUtils.sendPost(url, body, header);
            JSONObject jsonObject = (JSONObject) JSONObject.parse(sendPost);
            ;
            Map<String, Object> map = JSONObject.parseObject(jsonObject.toJSONString(), Map.class);
            couponUserId = map.get("data").toString();
            param.put("couponUserId", couponUserId);
            //查询优惠卷money、minPrice，跳转使用
            sb.delete(0, sb.length());
            sb.append(" SELECT money,min_price minPrice FROM eb_coupon_user where id=? ");
            List<Map<String, Object>> listcop = this.queryForList(conn_mall, sb.toString(), new Object[]{couponUserId});
            Map<String, Object> extData = new LinkedHashMap<String, Object>();
            extData.put("userCouponId", couponUserId);
            extData.put("money", listcop.get(0).get("money"));
            extData.put("minPrice", listcop.get(0).get("minPrice"));
            res.setExtData(extData);
        }
        saveActiveRecord(conn, req, res);
        return res;
    }

    public PageData saveActiveRecord(Connection conn, DataReq req, PageData res) throws SQLException {
        Map<String, Object> param = req.getReqData();
        param.put("tableName", "active_record");
        PageData sqlInfo = null;

        //判断是否超郭抽奖次数
        PageData resTmp = getActiveLeftCnt(conn, req);
        if (resTmp.getRetCode() != 0) {
            return resTmp;
        }
        Integer cnt = (Integer) resTmp.getExtData();
        if (cnt == 0) {
            res.setRetCode(9);
            res.setRetMessage("您已超过允许的抽奖次数！");
            return res;
        }

        String uuid = UUID.randomUUID().toString();
        param.put("uuid", uuid);
        sqlInfo = getInsertSQL(conn, req);
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());

        //如果获取的是现金红包，那么发红包
        String prizeName = (String) param.get("prizeName");
        if (prizeName != null && prizeName.indexOf("红包") >= 0) {
            String amount = DBUtils.getNumbers(prizeName);
            List<Map<String, Object>> list = this.queryForList(conn, "SELECT b.openid,b.`name`,c.id activeUuid ,c.activeName,b.phone from  active_record a INNER JOIN active_user b on a.userUuid=b.uuid INNER JOIN active_session c on a.activeUuid=c.uuid where b.openid is not null and b.openid<>'' and b.phone is not null and b.phone<>''  and a.uuid=?", new Object[]{uuid});
            if (list.size() == 1) {
                String openId = (String) list.get(0).get("openid");
                String phone = (String) list.get(0).get("phone");
                System.currentTimeMillis();
                String activityUUid = list.get(0).get("activeUuid").toString();
                String orderNo = "ORD" + phone + System.currentTimeMillis();
                String activityName = (String) list.get(0).get("activeName");
                String nickName = (String) list.get(0).get("name");
                String ip = StringUtils.isEmpty((String) param.get("remoteIp")) ? "127.0.0.1" : (String) param.get("remoteIp");
                resTmp = PayUtil.payToUser(ip, openId, amount, activityUUid, activityName, nickName, orderNo);
                if (resTmp.getRetCode() == 0) {
                    //更新支付表
                    this.update(conn, "update active_record set isPay=1 where uuid=?  ", new Object[]{uuid});
                }
                //				else {
                //					res.setRetCode(1);
                //					res.setRetMessage("红包于10分钟之内到账，请注意查收！");
                //					res.setKey(uuid);
                //					return res ;
                //				}
                System.out.println(JsonUtil.getStriFromObj("支付结果:" + JsonUtil.getStriFromObj(resTmp)));
            }
        }

        res.setRetCode(0);
        res.setRetMessage("OK");
        Map<String, Object> uuidMap = new HashMap<String, Object>();
        uuidMap.put("uuid", uuid);
        res.setData(uuidMap);
        return res;
    }

    public PageData saveUserModel(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("参数信息保存失败");
        param.put("tableName", "t_user_model_app");
        //String userId=(String) req.getReqData().get("userId");
        String userId = req.getUinfo().getId();
        param.put("userId", userId);
        param.put("key", "uuid");

        //删除该用户原来菜单
        StringBuffer sb = new StringBuffer();
        sb.append("UPDATE t_user_model_app set isdelete='1' where userId='").append(userId).append("' ");
        this.getJdbcTemplate_newg().update(sb.toString());

        //保存新菜单
        String[] modelArr = (String[]) req.getReqData().get("modelJson");
        PageData sqlInfo = null;
        for (int i = 0; i < modelArr.length; i++) {
            String uuid = UUID.randomUUID().toString();
            param.put("uuid", uuid);
            param.put("modelId", modelArr[i]);
            param.put("px", i);
            sqlInfo = getInsertSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.update(conn, sb_field.toString(), listData.toArray());
        }

        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getUserModel(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        //String userId=(String) req.getReqData().get("userId");
        String userId = req.getUinfo().getId();

        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT a.modelId,b.`name` from t_user_model_app a LEFT JOIN t_model b on a.modelId=b.model_id where isdelete='0' and a.userId='")
                .append(userId)
                .append("' order by px asc");
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});

        //如果没有值，取默认菜单前7个
        if (list.size() == 0) {
            sb = new StringBuffer();
            sb.append(" SELECT a.model_id modelId,b.`name` from t_role_model a LEFT JOIN t_model b on a.model_id=b.model_id ");
            sb.append(" where role_id=(SELECT role_id FROM t_user where id='").append(userId).append("') ");
            sb.append(" and b.isappuse='1' and b.`url`<>'#' and b.`name`<>'' and b.`name` is not null ORDER BY a.model_id asc LIMIT 7 ");
            list = this.queryForList(conn, sb.toString(), new Object[]{});
        }

        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public static void main(String[] args) {
        String prizeName = "红包0.01元";
        String openId = "oAe385I5VH4mtkLEyIYGtCO9AO74";
        String phone = "13811766622";
        System.currentTimeMillis();
        String activityUUid = "66363334444";
        String orderNo = "ORD" + phone + System.currentTimeMillis();
        String activityName = "红包雨";
        String nickName = "吴玉兴";
        String ip = "127.0.0.1";
        String amount = "1";
        PageData resTmp = PayUtil.payToUser(ip, openId, amount, activityUUid, activityName, nickName, orderNo);
        System.out.println(JsonUtil.getStriFromObj(resTmp));
    }

    public PageData copyActiveSession(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("复制活动失败");
        PageData sqlInfo = null;
        StringBuffer sb = new StringBuffer();
        String uuid = (String) param.get("uuid");
        String uuidCopy = UUID.randomUUID().toString();
        if (!StringUtils.isEmpty(uuid)) {
            sb.append(" SELECT companyId,activeName,activeTimeStart,activeTimeEnd,activeCjcs,activePic," + "titleUrl,backgroundUrl,activeRule,mineFormMesg,indexTemplate,activityUuid,forward,maxForward" + " from active_session ");
            sb.append(" where uuid='").append(uuid).append("' ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});

            if (list.size() > 0) {
                param.put("tableName", "active_session");
                param.put("uuid", uuidCopy);
                param.put("companyId", list.get(0).get("companyId"));
                param.put("activeName", list.get(0).get("activeName") + "_复制");
                param.put("activeTimeStart", list.get(0).get("activeTimeStart"));
                param.put("activeTimeEnd", list.get(0).get("activeTimeEnd"));
                param.put("activeCjcs", list.get(0).get("activeCjcs"));
                param.put("activePic", list.get(0).get("activePic"));
                param.put("titleUrl", list.get(0).get("titleUrl"));
                param.put("backgroundUrl", list.get(0).get("backgroundUrl"));
                param.put("activeRule", list.get(0).get("activeRule"));
                param.put("mineFormMesg", list.get(0).get("mineFormMesg"));
                param.put("indexTemplate", list.get(0).get("indexTemplate"));
                param.put("activityUuid", list.get(0).get("activityUuid"));
                param.put("forward", list.get(0).get("forward"));
                param.put("maxForward", list.get(0).get("maxForward"));
                sqlInfo = getInsertSQL(conn, req);
                if (sqlInfo.getRetCode() != 0) {
                    return sqlInfo;
                }
                final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
                final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
                this.update(conn, sb_field.toString(), listData.toArray());
                res.setRetCode(0);
                res.setRetMessage("OK");
            }
        } else {
            return res;
        }

        return res;
    }

    public PageData getWchatMenu(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        Map<String, Object> extData = new LinkedHashMap<String, Object>();

        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM active_wchat_menu where activetype='imgUrls' ");
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        extData.put("imgUrls", list);

        sb = new StringBuffer();
        sb.append(" SELECT * FROM active_wchat_menu where activetype='imgsActive' ");
        list = this.queryForList(conn, sb.toString(), new Object[]{});
        extData.put("imgsActive", list);

        sb = new StringBuffer();
        sb.append(" SELECT * FROM active_wchat_menu where activetype='dapinglist' ");
        list = this.queryForList(conn, sb.toString(), new Object[]{});
        if (list.size() > 0) {
            for (int j = 0; j < list.size(); j++) {
                String StringSession = list.get(j).get("imglist").toString();
                JSONArray JSONArray = (JSONArray) JSON.parse(StringSession);
                list.get(j).put("imglist", JSONArray);
            }
        }
        extData.put("dapinglist", list);

        sb = new StringBuffer();
        sb.append(" SELECT * FROM active_wchat_menu where activetype='haibaolist' ");
        list = this.queryForList(conn, sb.toString(), new Object[]{});
        if (list.size() > 0) {
            for (int j = 0; j < list.size(); j++) {
                String StringSession = list.get(j).get("imglist").toString();
                list.get(j).put("imglist", StringSession.split(","));
            }
        }
        extData.put("haibaolist", list);

        res.setExtData(extData);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getPCWCHATActiveList(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");

        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        sb.append(" select * from pc_wchat_active a ");
        sb.append(" where a.isdelete=0 and a.companyId='" + req.getUinfo().getCompanyId() + "' ");
        if (!StringUtils.isAnyEmpty((String) param.get("activeName"))) {
            sb.append(" and a.activeName like '%").append(param.get("activeName")).append("%'");
        }
        sb.append(" order by a.createTime desc ");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData delPCWCHATActive(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("删除活动失败");
        String activeUuid = (String) param.get("activeUuid");
        if (!StringUtils.isEmpty(activeUuid)) {
            StringBuffer sb = new StringBuffer();
            sb.append("update pc_wchat_active set isdelete=1 where activeUuid='").append(activeUuid).append("' ");
            this.update(conn, sb.toString(), new Object[]{});
            res.setRetCode(0);
            res.setRetMessage("OK");
        } else {

            return res;
        }

        return res;
    }

    public PageData getYaoyiyaoWinList(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取中奖名单失败");
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        sb.append(" select b.name,b.phone,a.shakeCnt,a.activeRound,c.prizeDrade,c.prizeName,a.createTime from pc_wchat_active_hjuser a ");
        sb.append(" left join active_user b on a.userUuid=b.uuid ");
        sb.append(" left join pc_wchat_active_reward c on a.prizeUuid=c.prizeUuid ");
        sb.append(" where a.isdelete=0 and a.companyId='" + req.getUinfo().getCompanyId() + "' ");
        sb.append(" and a.prizeUuid is not null and a.prizeUuid<>'' ");//只查询获奖记录
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        if (!StringUtils.isEmpty((String) param.get("key"))) {
            sb.append(" and (b.name like '%")
                    .append(param.get("key"))
                    .append("%' or b.phone='")
                    .append(param.get("key"))
                    .append("') ");
        }
        sb.append(" order by a.activeRound,a.shakeCnt desc ");
        sb = DBUtils.sql_check(sb);
        sbfrom.append(sb.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
        sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getPCWCHATActiveDetail(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取活动信息失败");
        Map<String, Object> map = new HashMap();
        //获取活动信息
        StringBuffer sb = new StringBuffer();
        sb.append(" select * from pc_wchat_active a ");
        sb.append(" where a.isdelete=0 and a.companyId='").append(req.getUinfo().getCompanyId()).append("' ");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        List<Map<String, Object>> activeList = this.queryForList(conn, sb.toString(), new Object[]{});
        map.put("activeInfo", activeList.get(0));
        //企业logo
        String brandLogoName = activeList.get(0).get("brandLogo").toString();
        String brandLogoUrl = OssUtil.getImgSignURL(brandLogoName, 0);
        Map<String, String> brandLogoImg = new HashMap<String, String>();
        brandLogoImg.put("name", brandLogoName);
        brandLogoImg.put("url", brandLogoUrl);
        ArrayList<Object> brandLogoMap = new ArrayList<Object>();
        brandLogoMap.add(0, brandLogoImg);
        map.put("brandLogoMap", brandLogoMap);
        //获取奖品奖项列表
        sb.delete(0, sb.length());
        sb.append(" select * from pc_wchat_active_reward a ");
        sb.append(" where a.isdelete=0 and a.companyId='").append(req.getUinfo().getCompanyId()).append("' ");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        sb.append(" order by a.id asc");
        List<Map<String, Object>> prizeList = this.queryForList(conn, sb.toString(), new Object[]{});
        map.put("prizeList", prizeList);
        res.setExtData(map);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData savePCWCHATActive(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        //保存活动信息
        DataReq activeReq = new DataReq();
        HashMap<String, Object> activeData = new HashMap();
        JSONObject activeInfo = (JSONObject) param.get("activeInfo");
        activeData = (HashMap<String, Object>) JSONObject.parseObject(activeInfo.toJSONString(), Map.class);
        activeData.put("companyId", req.getUinfo().getCompanyId());
        activeReq.setReqData(activeData);
        activeReq.setUinfo(req.getUinfo());
        res = savePCWCHATActiveInfo(conn, activeReq);
        if (res.getRetCode() != 0) {
            return res;
        }
        //获取活动uuid
        String activeUuid = res.getKey();
        //保存PC奖项信息
        DataReq prizeReq = new DataReq();
        HashMap<String, Object> prizeData = new HashMap();
        prizeData.put("activeUuid", activeUuid);
        prizeData.put("companyId", req.getUinfo().getCompanyId());
        JSONArray prizeArray = (JSONArray) param.get("prizeList");
        //封装prizeList
        Iterator prizeIter = prizeArray.listIterator();
        List<Map<String, Object>> prizeList = new ArrayList();
        while (prizeIter.hasNext()) {
            Map<String, Object> obj = (Map<String, Object>) prizeIter.next();
            prizeList.add(obj);
        }
        prizeData.put("prizeList", prizeList);
        prizeReq.setReqData(prizeData);
        prizeReq.setUinfo(req.getUinfo());
        res = savePCWCHATAPrizeInfo(conn, prizeReq);
        if (res.getRetCode() != 0) {
            return res;
        }
        res.setKey(activeUuid);
        return res;
    }

    public PageData savePCWCHATActiveInfo(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存活动信息失败");
        param.put("tableName", "pc_wchat_active");
        PageData sqlInfo = null;
        String activeUuid = (String) param.get("activeUuid");
        if (activeUuid == null || "".equals(activeUuid)) {
            param.put("isdelete", "0");
            activeUuid = UUID.randomUUID().toString();
            param.put("activeUuid", activeUuid);
            sqlInfo = getInsertSQL(conn, req);
        } else {
            param.put("keys", "activeUuid");
            sqlInfo = getUpdateSQL(conn, req);
        }
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());
        res.setKey(activeUuid);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData savePCWCHATAPrizeInfo(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存奖项信息失败");
        param.put("tableName", "pc_wchat_active_reward");
        PageData sqlInfo = null;
        //根据活动ID删除奖项
        StringBuffer sb = new StringBuffer();
        sb.append("delete from pc_wchat_active_reward where activeUuid='").append(param.get("activeUuid")).append("' ");
        this.update(conn, sb.toString(), new Object[]{});
        List<Map<String, Object>> prizeList = (List<Map<String, Object>>) param.get("prizeList");
        for (int i = 0; i < prizeList.size(); i++) {
            String prizeUuid = (String) prizeList.get(i).get("prizeUuid");
            param.put("prizeDrade", prizeList.get(i).get("prizeDrade"));
            param.put("prizeName", prizeList.get(i).get("prizeName"));
            param.put("prizeNumber", prizeList.get(i).get("prizeNumber"));
            param.put("prizeImg", prizeList.get(i).get("prizeImg"));
            param.put("prizeScopeStart", prizeList.get(i).get("prizeScopeStart"));
            param.put("prizeScopeEnd", prizeList.get(i).get("prizeScopeEnd"));
            param.put("isdelete", "0");
            if (prizeUuid == null || "".equals(prizeUuid)) {
                prizeUuid = UUID.randomUUID().toString();
            }
            param.put("prizeUuid", prizeUuid);
            sqlInfo = getInsertSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.update(conn, sb_field.toString(), listData.toArray());
        }
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getPCWCHATActiveShow(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取活动预览信息失败");
        Map<String, Object> map = new HashMap();

        StringBuffer sb = new StringBuffer();
        //获取活动信息
        sb.delete(0, sb.length());
        sb.append(" select * from pc_wchat_active a ");
        sb.append(" where a.isdelete=0 and a.companyId='").append(req.getUinfo().getCompanyId()).append("'");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        List<Map<String, Object>> activeInfo = this.queryForList(conn, sb.toString(), new Object[]{});
        //企业logo
        String brandLogoName = activeInfo.get(0).get("brandLogo").toString();
        String brandLogoUrl = OssUtil.getImgSignURL(brandLogoName, 0);
        activeInfo.get(0).put("brandLogoUrl", brandLogoUrl);
        //查询已经已经玩过几轮了，将下一轮反给前端
        sb.delete(0, sb.length());
        sb.append(" select activeRound from pc_wchat_active_hjuser a ");
        sb.append(" where a.isdelete=0 and a.companyId='" + req.getUinfo().getCompanyId() + "' ");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        sb.append(" group by activeRound order by a.activeRound desc limit 1 ");
        sb = DBUtils.sql_check(sb);
        int activeRound = this.queryForInt(conn, sb.toString(), new Object[]{});
        if (activeRound > 0) {
            activeInfo.get(0).put("nowActiveRound", activeRound + 1);//已经玩过的，轮数为玩过的轮数加1
        } else {
            activeInfo.get(0).put("nowActiveRound", 1);//没有玩过的为第1轮
        }
        map.put("activeInfo", activeInfo.size() > 0 ? activeInfo.get(0) : null);
        //获取奖品奖项列表
        sb.delete(0, sb.length());
        sb.append(" select * from pc_wchat_active_reward a ");
        sb.append(" where a.isdelete=0 and a.companyId='").append(req.getUinfo().getCompanyId()).append("' ");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        sb.append(" order by a.id asc");
        List<Map<String, Object>> prizeList = this.queryForList(conn, sb.toString(), new Object[]{});
        //图片路径生成
        if (prizeList.size() > 0) {
            for (int j = 0; j < prizeList.size(); j++) {
                String titleName = prizeList.get(j).get("prizeImg").toString();
                String titleUrl = OssUtil.getImgSignURL(titleName, 2);
                prizeList.get(j).put("url", titleUrl);
            }
        }
        map.put("prizeList", prizeList);
        res.setExtData(map);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getPCWCHATActiveShow_xcx(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取活动预览信息失败");
        Map<String, Object> map = new HashMap();
        StringBuffer sb = new StringBuffer();
        //获取活动信息
        sb.delete(0, sb.length());
        sb.append(" select * from pc_wchat_active a ");
        sb.append(" where a.isdelete=0 and a.companyId='").append(param.get("companyId")).append("'");
        //sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        sb.append(" and a.id='").append(param.get("id")).append("' ");//uuid太长，用id
        List<Map<String, Object>> activeInfo = this.queryForList(conn, sb.toString(), new Object[]{});
        if (activeInfo.size() > 0) {
            String activeUuid = activeInfo.get(0).get("activeUuid").toString();
            String companyId = activeInfo.get(0).get("companyId").toString();
            //企业logo
            String brandLogoName = activeInfo.get(0).get("brandLogo").toString();
            String brandLogoUrl = OssUtil.getImgSignURL(brandLogoName, 0);
            activeInfo.get(0).put("brandLogoUrl", brandLogoUrl);
            //查询已经已经玩过几轮了，将下一轮反给前端
            sb.delete(0, sb.length());
            sb.append(" select activeRound from pc_wchat_active_hjuser a ");
            sb.append(" where a.isdelete=0 and a.companyId='").append(companyId).append("' ");
            sb.append(" and a.activeUuid='").append(activeUuid).append("' ");
            sb.append(" group by activeRound order by a.activeRound desc limit 1 ");
            sb = DBUtils.sql_check(sb);
            int activeRound = this.queryForInt(conn, sb.toString(), new Object[]{});
            if (activeRound > 0) {
                activeInfo.get(0).put("nowActiveRound", activeRound + 1);//已经玩过的，轮数为玩过的轮数加1
            } else {
                activeInfo.get(0).put("nowActiveRound", 1);//没有玩过的为第1轮
            }
            map.put("activeInfo", activeInfo.size() > 0 ? activeInfo.get(0) : null);
            //获取奖品奖项列表
            sb.delete(0, sb.length());
            sb.append(" select * from pc_wchat_active_reward a ");
            sb.append(" where a.isdelete=0 and a.companyId='").append(companyId).append("' ");
            sb.append(" and a.activeUuid='").append(activeUuid).append("' ");
            sb.append(" order by a.id asc");
            List<Map<String, Object>> prizeList = this.queryForList(conn, sb.toString(), new Object[]{});
            //图片路径生成
            if (prizeList.size() > 0) {
                for (int j = 0; j < prizeList.size(); j++) {
                    String titleName = prizeList.get(j).get("prizeImg").toString();
                    String titleUrl = OssUtil.getImgSignURL(titleName, 2);
                    prizeList.get(j).put("url", titleUrl);
                }
            }
            map.put("prizeList", prizeList);
        }
        res.setExtData(map);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getPCWCHATPersonShow(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取参与用户失败");
        StringBuffer sb = new StringBuffer();
        if (!StringUtils.isEmpty((String) param.get("islook"))) {
            if ("look".equals(param.get("islook"))) { //预览
                sb.append("select l.id,l.image,l.thumb_image,l.`name`,l.phone,l.flag,l.serial,concat(left(l.phone,3),'****',right(l.phone,4)) tmPhone from ("); //手机号脱敏 select concat(left(l.phone,3),'****',right(l.phone,4))
                sb.append("select a.uuid id," + "if(a.pic<>'' and a.pic is not null,a.pic,'') image," + "if(a.pic<>'' and a.pic is not null,a.pic,'') thumb_image,a.name,a.phone,2 flag,a.serial\n" + " from pc_active_default_user a\n" + " where a.isdelete=0 \n");
                sb.append("union select a.uuid id," + "if(b.userPic<>'' and b.userPic is not null,b.userPic,'') image," + "if(b.userPic<>'' and b.userPic is not null,b.userPic,'') thumb_image," + "a.companycontact name,a.cluephone phone,1 flag,a.serial\n" + "from clue a\n" + "left join active_user b on a.openid=b.openid " + "where a.isdelete=0\n" + "and a.cluebelongtocompany='")
                        .append(param.get("companyId"))
                        .append("' ")
                        .append(" and a.advertisementId='")
                        .append(param.get("activeUuid"))
                        .append("' ");
                sb.append(") l where 1=1 ");
                //根据uuid查询创建时间之后的用户信息
                if (!StringUtils.isEmpty((String) param.get("uuid"))) {
                    sb.append("and l.serial >=(select serial from clue where ")
                            .append("uuid='")
                            .append(param.get("uuid"))
                            .append("') and l.id!='")
                            .append(param.get("uuid"))
                            .append("'");
                }
                sb.append("order by l.flag desc,l.serial asc ");
            } else { //发布
                sb.append("select a.uuid id," + "if(b.userPic<>'' and b.userPic is not null,b.userPic,'') image," + "if(b.userPic<>'' and b.userPic is not null,b.userPic,'') thumb_image," + "a.companycontact name,a.cluephone phone,concat(left(a.cluephone,3),'****',right(a.cluephone,4)) tmPhone \n" + " from clue a\n" + "left join active_user b on a.openid=b.openid " + " where a.isdelete=0 " + " and a.cluebelongtocompany='")
                        .append(param.get("companyId"))
                        .append("'\n" + " and a.advertisementId='")
                        .append(param.get("activeUuid"))
                        .append("' ");
                //根据uuid查询创建时间之后的用户信息
                if (!StringUtils.isEmpty((String) param.get("uuid"))) {
                    sb.append("and a.serial >=(select serial from clue where ")
                            .append("uuid='")
                            .append(param.get("uuid"))
                            .append("') and a.uuid!='")
                            .append(param.get("uuid"))
                            .append("'");
                }
                sb.append(" order by a.serial asc,a.modifyTime asc ");
            }
        }
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        Random random = new Random();
        for (Map<String, Object> map : list) {
            String imageString = (String) map.get("image");
            if (!StringUtils.isEmpty(imageString)) {
                String ossImage = OssUtil.getImgSignURL(imageString, 0);
                if (StringUtils.isEmpty(ossImage)) {
                    int sj = random.nextInt(30) + 1;
                    //头像名
                    String imgUrl = "crm/avatars/cl" + sj + ".jpg";
                    StringBuffer buffer = new StringBuffer();
                    buffer.append("update active_user set userPic = '")
                            .append(imgUrl)
                            .append("' where phone = '")
                            .append(map.get("phone"))
                            .append("'");
                    this.update(conn, buffer.toString(), new Object[]{});
                    ossImage = OssUtil.getImgSignURL(imgUrl, 0);
                    map.put("image", ossImage);
                } else {
                    map.put("image", ossImage);
                }
            } else {
                int sj = random.nextInt(30) + 1;
                //头像名
                String imgUrl = "crm/avatars/cl" + sj + ".jpg";
                StringBuffer buffer = new StringBuffer();
                buffer.append("update active_user set userPic = '")
                        .append(imgUrl)
                        .append("' where phone = '")
                        .append(map.get("phone"))
                        .append("'");
                this.update(conn, buffer.toString(), new Object[]{});
                String ossImage = OssUtil.getImgSignURL(imgUrl, 0);
                map.put("image", ossImage);
            }
        }
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getPCWCHATTimeShake(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        StringBuffer sb = new StringBuffer();
        sb.append(" select b.name,b.phone,sum(a.shakeCnt) count,sum(if(sfzx='1',a.shakeCnt,0)) addcount from pc_wchat_active_shake a ");
        sb.append(" left join active_user b on a.userUuid=b.uuid ");
        sb.append(" where a.isdelete=0 and a.companyId='" + req.getUinfo().getCompanyId() + "' ");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        sb.append(" and a.activeRound=").append(param.get("activeRound"));
        sb.append(" group by a.userUuid ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData savePCWCHATChartShake(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        PageData sqlInfo = null;
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        //查询摇晃次数总排名
        StringBuffer sb = new StringBuffer();
        sb.append(" select sum(a.shakeCnt) shakeCnt,a.userUuid from pc_wchat_active_shake a ");
        sb.append(" where a.isdelete=0 and a.companyId='" + req.getUinfo().getCompanyId() + "' ");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        sb.append(" and a.activeRound=").append(param.get("activeRound"));
        sb.append(" group by a.userUuid order by sum(a.shakeCnt) desc ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> listShake = this.queryForList(conn, sb.toString(), new Object[]{});
        //查询该活动奖品信息
        sb.delete(0, sb.length());
        sb.append(" select a.prizeUuid,a.prizeScopeStart,a.prizeScopeEnd from pc_wchat_active_reward a ");
        sb.append(" where a.isdelete=0 and a.companyId='" + req.getUinfo().getCompanyId() + "' ");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        sb.append(" order by a.prizeScopeStart asc ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> listPrize = this.queryForList(conn, sb.toString(), new Object[]{});
        //查询该活动一共几个获奖名额
        sb.delete(0, sb.length());
        sb.append(" select a.activeAwards from pc_wchat_active a ");
        sb.append(" where a.isdelete=0 and a.companyId='" + req.getUinfo().getCompanyId() + "' ");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        sb = DBUtils.sql_check(sb);
        int activeAwards = this.queryForInt(conn, sb.toString(), new Object[]{});
        //保存该轮活动获奖信息
        String[] ScopeArr = new String[activeAwards];
        for (int i = 0; i < listPrize.size(); i++) {
            //根据设定的奖品范围写入获奖信息
            int prizeScopeStart = Integer.parseInt((String) listPrize.get(i).get("prizeScopeStart"));
            int prizeScopeEnd = Integer.parseInt((String) listPrize.get(i).get("prizeScopeEnd"));
            for (int j = prizeScopeStart; j < prizeScopeEnd + 1; j++) {
                ScopeArr[j - 1] = (String) listPrize.get(i).get("prizeUuid");
            }
        }
        param.put("tableName", "pc_wchat_active_hjuser");
        param.put("activeUuid", param.get("activeUuid"));
        param.put("activeRound", param.get("activeRound"));
        param.put("companyId", req.getUinfo().getCompanyId());
        for (int i = 0; i < listShake.size(); i++) {
            param.put("shakeIndex", i + 1);//保存排名
            if (i < ScopeArr.length) {//保存获奖信息
                param.put("userUuid", listShake.get(i).get("userUuid"));
                param.put("shakeCnt", listShake.get(i).get("shakeCnt"));
                param.put("prizeUuid", ScopeArr[i]);
            } else {//没有奖品的只保存排名
                param.put("userUuid", listShake.get(i).get("userUuid"));
                param.put("shakeCnt", listShake.get(i).get("shakeCnt"));
                param.put("prizeUuid", "");
            }
            sqlInfo = getInsertSQL(conn, req);
            if (sqlInfo.getRetCode() != 0) {
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.update(conn, sb_field.toString(), listData.toArray());
        }
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getPCWCHATChartShake(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        StringBuffer sb = new StringBuffer();
        sb.append(" select b.name,b.phone,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') image,a.shakeCnt from pc_wchat_active_hjuser a ");
        sb.append(" left join active_user b on a.userUuid=b.uuid ");
        sb.append(" where a.isdelete=0 and a.companyId='" + req.getUinfo().getCompanyId() + "' ");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        sb.append(" and a.activeRound=").append(param.get("activeRound"));
        sb.append(" order by a.shakeCnt desc ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        Random random = new Random();
        for (Map<String, Object> map : list) {
            String imageString = (String) map.get("image");
            if (!StringUtils.isEmpty(imageString)) {
                String ossImage = OssUtil.getImgSignURL(imageString, 0);
                if (StringUtils.isEmpty(ossImage)) {
                    int sj = random.nextInt(30) + 1;
                    //头像名
                    String imgUrl = "crm/avatars/cl" + sj + ".jpg";
                    StringBuffer buffer = new StringBuffer();
                    buffer.append("update active_user set userPic = '")
                            .append(imgUrl)
                            .append("' where phone = '")
                            .append(map.get("phone"))
                            .append("'");
                    this.update(conn, buffer.toString(), new Object[]{});
                    ossImage = OssUtil.getImgSignURL(imgUrl, 0);
                    map.put("image", ossImage);
                } else {
                    map.put("image", ossImage);
                }
            } else {
                int sj = random.nextInt(30) + 1;
                //头像名
                String imgUrl = "crm/avatars/cl" + sj + ".jpg";
                StringBuffer buffer = new StringBuffer();
                buffer.append("update active_user set userPic = '")
                        .append(imgUrl)
                        .append("' where phone = '")
                        .append(map.get("phone"))
                        .append("'");
                this.update(conn, buffer.toString(), new Object[]{});
                String ossImage = OssUtil.getImgSignURL(imgUrl, 0);
                map.put("image", ossImage);
            }
        }
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getPCWCHATChartShake_xcx(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        StringBuffer sb = new StringBuffer();
        sb.append(" select b.name,b.phone,if(b.userPic<>'' and b.userPic is not null,b.userPic,'') image,a.shakeCnt from pc_wchat_active_hjuser a ");
        sb.append(" left join active_user b on a.userUuid=b.uuid ");
        sb.append(" where a.isdelete=0 and a.companyId='" + param.get("companyId") + "' ");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        sb.append(" and a.activeRound=").append(param.get("activeRound"));
        sb.append(" order by a.shakeCnt desc ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        Random random = new Random();
        for (Map<String, Object> map : list) {
            String imageString = (String) map.get("image");
            if (!StringUtils.isEmpty(imageString)) {
                String ossImage = OssUtil.getImgSignURL(imageString, 0);
                if (StringUtils.isEmpty(ossImage)) {
                    int sj = random.nextInt(30) + 1;
                    //头像名
                    String imgUrl = "crm/avatars/cl" + sj + ".jpg";
                    StringBuffer buffer = new StringBuffer();
                    buffer.append("update active_user set userPic = '")
                            .append(imgUrl)
                            .append("' where phone = '")
                            .append(map.get("phone"))
                            .append("'");
                    this.update(conn, buffer.toString(), new Object[]{});
                    ossImage = OssUtil.getImgSignURL(imgUrl, 0);
                    map.put("image", ossImage);
                } else {
                    map.put("image", ossImage);
                }
            } else {
                int sj = random.nextInt(30) + 1;
                //头像名
                String imgUrl = "crm/avatars/cl" + sj + ".jpg";
                StringBuffer buffer = new StringBuffer();
                buffer.append("update active_user set userPic = '")
                        .append(imgUrl)
                        .append("' where phone = '")
                        .append(map.get("phone"))
                        .append("'");
                this.update(conn, buffer.toString(), new Object[]{});
                String ossImage = OssUtil.getImgSignURL(imgUrl, 0);
                map.put("image", ossImage);
            }
        }
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData savePCWCHATActiveUser(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("参数信息保存失败");
        param.put("tableName", "active_user");
        PageData sqlInfo = null;
        String uuid = (String) param.get("uuid");
        param.put("yhlb", "01");
        if (uuid == null || "".equals(uuid)) {
            uuid = UUID.randomUUID().toString();
            param.put("uuid", uuid);
            sqlInfo = getInsertSQL(conn, req);
        } else {
            param.put("keys", "uuid");
            sqlInfo = getUpdateSQL(conn, req);
        }
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());
        res.setRetCode(0);
        res.setRetMessage("OK");
        res.setKey(uuid);
        return res;
    }

    public PageData savePCWCHATAShake(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        PageData sqlInfo = null;
        res.setRetCode(9);
        res.setRetMessage("保存摇晃信息失败");
        //先把该活动参与人是否最新的数据改为0
        StringBuffer sb = new StringBuffer();
        sb.delete(0, sb.length());
        sb.append(" update pc_wchat_active_shake set sfzx='0' ");
        sb.append(" where isdelete=0 and companyId='").append(param.get("companyId")).append("' ");
        sb.append(" and activeUuid='").append(param.get("activeUuid")).append("' ");
        sb.append(" and activeRound=").append(param.get("activeRound"));
        sb.append(" and userUuid='").append(param.get("userUuid")).append("' ");
        this.update(conn, sb.toString(), new Object[]{});
        //保存最新的摇晃数据
        param.put("tableName", "pc_wchat_active_shake");
        param.put("sfzx", "1");
        sqlInfo = getInsertSQL(conn, req);
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getPCWCHATUserhj(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        StringBuffer sb = new StringBuffer();
        sb.append(" select a.activeRound,a.shakeCnt,a.shakeIndex,a.prizeUuid,b.prizeName,b.prizeDrade,b.prizeImg from pc_wchat_active_hjuser a ");
        sb.append(" left join pc_wchat_active_reward b on a.prizeUuid=b.prizeUuid ");
        sb.append(" where a.isdelete=0 and a.companyId='" + param.get("companyId") + "' ");
        sb.append(" and a.activeUuid='").append(param.get("activeUuid")).append("' ");
        sb.append(" and a.userUuid='").append(param.get("userUuid")).append("' ");
        if (!StringUtils.isEmpty((String) param.get("activeRound"))) {//个人当前轮成绩
            sb.append(" and a.activeRound=").append(param.get("activeRound"));
        } else {//个人获奖成绩
            sb.append(" and a.prizeUuid <> '' and a.prizeUuid is not null ");
            sb.append(" order by a.activeRound ");
        }
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        //图片路径生成
        if (list.size() > 0) {
            for (int j = 0; j < list.size(); j++) {
                String titleName = list.get(j).get("prizeImg").toString();
                String titleUrl = OssUtil.getImgSignURL(titleName, 2);
                list.get(j).put("url", titleUrl);
            }
        }
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getPCWCHATUserimg(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        StringBuffer sb = new StringBuffer();
        sb.append(" select uuid userUuid,name,phone,if(userPic<>'' and userPic is not null,userPic,'') image from active_user where openid= '")
                .append(param.get("openid"))
                .append("' ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        Random random = new Random();
        for (Map<String, Object> map : list) {
            String imageString = (String) map.get("image");
            if (!StringUtils.isEmpty(imageString)) {
                String ossImage = OssUtil.getImgSignURL(imageString, 0);
                if (StringUtils.isEmpty(ossImage)) {
                    int sj = random.nextInt(30) + 1;
                    //头像名
                    String imgUrl = "crm/avatars/cl" + sj + ".jpg";
                    StringBuffer buffer = new StringBuffer();
                    buffer.append("update active_user set userPic = '")
                            .append(imgUrl)
                            .append("' where phone = '")
                            .append(map.get("phone"))
                            .append("'");
                    this.update(conn, buffer.toString(), new Object[]{});
                    ossImage = OssUtil.getImgSignURL(imgUrl, 0);
                    map.put("image", ossImage);
                } else {
                    map.put("image", ossImage);
                }
            } else {
                int sj = random.nextInt(30) + 1;
                //头像名
                String imgUrl = "crm/avatars/cl" + sj + ".jpg";
                StringBuffer buffer = new StringBuffer();
                buffer.append("update active_user set userPic = '")
                        .append(imgUrl)
                        .append("' where phone = '")
                        .append(map.get("phone"))
                        .append("'");
                this.update(conn, buffer.toString(), new Object[]{});
                String ossImage = OssUtil.getImgSignURL(imgUrl, 0);
                map.put("image", ossImage);
            }
            //查询用户是否填报资料
            sb = new StringBuffer();
            sb.append(" select count(1) customerCnt from `customer` where openId =? and activeUuid=? and ( businessdirection is not null and businessdirection<>'') ");
            int customerCnt = this.queryForInt(conn, sb.toString(), new Object[]{param.get("openid"), param.get("activeUuid")});
            map.put("customerCnt", customerCnt);
        }
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData saveActiveIsStart(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        PageData sqlInfo = null;
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        param.put("tableName", "pc_wchat_active_start");
        param.put("companyId", req.getUinfo().getCompanyId());
        sqlInfo = getInsertSQL(conn, req);
        if (sqlInfo.getRetCode() != 0) {
            return sqlInfo;
        }
        final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
        final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
        this.update(conn, sb_field.toString(), listData.toArray());
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData getActiveIsStart(Connection conn, DataReq req) throws SQLException {
        PageData res = new PageData();
        Map<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("获取信息失败");
        StringBuffer sb = new StringBuffer();
        sb.append(" select isStart from pc_wchat_active_start where activeUuid= '")
                .append(param.get("activeUuid"))
                .append("' ")
                .append(" and activeRound=")
                .append(param.get("activeRound"))
                .append(" and companyId= '")
                .append(param.get("companyId"))
                .append("' ");
        sb.append(" order by createTime desc limit 1 ");
        sb = DBUtils.sql_check(sb);
        List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
        res.setPage(list);
        res.setRetCode(0);
        res.setRetMessage("OK");
        return res;
    }

    public PageData modifyState(Connection conn, DataReq req) throws SQLException {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("优惠卷状态修改失败");
        String type = (String) param.get("type");//01是退回 02是使用
        String userCouponId = (String) param.get("userCouponId");
        String orderNo = (String) param.get("orderNo");
        String merId = (String) param.get("merId");
        if (!StringUtils.isEmpty(orderNo)) {
            //如果传了订单号，去商城查询用户优惠卷号
            StringBuffer sb = new StringBuffer();
            sb.append(" SELECT b.id from eb_order a \n " + " LEFT JOIN eb_coupon_user b on a.uid=b.uid and a.coupon_price=b.money \n " + " where a.order_no=? and b.mer_id=? and b.`status`=1 ");
            List<Map<String, Object>> list = this.getJdbcTemplate_mall()
                    .queryForList(sb.toString(), new Object[]{orderNo, merId});
            if (list.size() > 0) {
                userCouponId = list.get(0).get("id").toString();
            }
        }
        if (!StringUtils.isEmpty(userCouponId) && !userCouponId.equals("") && !userCouponId.equals("null") && null != userCouponId) {
            StringBuffer sb = new StringBuffer();
            sb.append(" update active_record set prizeType=? where couponUserId=? ");
            this.update(conn, sb.toString(), new Object[]{type, userCouponId});
            res.setRetCode(0);
            res.setRetMessage("OK");
        } else {
            return res;
        }
        return res;
    }

}
